import { BooleanType, ResponseCode } from './../common/type/enum';
import { CrudService } from '@/shared/crud.service';
import { Inject, Injectable } from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { Brackets, DataSource, EntityManager, InsertResult, QueryRunner, SelectQueryBuilder } from 'typeorm';
import {
    AuditDto,
    AuditInvoiceFeedetDto,
    AuditInvoicePayDto,
    BusinessReportDto,
    BusiReportDto,
    CategoryDto,
    CurrencyDto,
    ExchangeRateDto,
    FeeAuditDto,
    FeeCodDto,
    FeeCodModDto,
    FeedetDto,
    FeeEleDto,
    FeeEleLovDto,
    FeeModDto,
    FeeModEleDto,
    FeeReportDefineDto,
    FeeTypDto,
    InitLovDto,
    InvoiceDetailDto,
    InvoiceDto,
    InvoiceFeedetAuditDto,
    InvoicePayAuditDto,
    LeaseFeeReportDto,
    LeaseFeeUsdInvoiceDto,
    PayDto,
    PrePayDto,
    ProtocolDto,
    ProtocolRatDto,
    queryElesByFeeModDto,
    queryModsByFeeCodDto,
} from './dto/create-business.dto';
import {
    DeleteDto,
    DeleteStrPrimaryDto,
    FormFieldSelectOption,
    ResponseJson,
    SqlQueryDto,
} from '@/common/type/app.dto';
import {
    Category,
    CfsFeeReport,
    Currency,
    ExchangeRate,
    FeeAudit,
    FeeCod,
    FeeCodMod,
    FeeDet,
    FeeEle,
    FeeEleLov,
    FeeMod,
    FeeModEle,
    FeeReportCfg,
    FeeReportManager,
    FeeTyp,
    FeeTypCod,
    Invoice,
    InvoiceAuditFeedet,
    InvoiceAuditPay,
    InvoiceDetail,
    LeaseFeeReport,
    /* FeeTypCod, */
    Pay,
    PrePay,
    Protocol,
    ProtocolRat,
    VAudit,
    VAuditFeedet,
    VAuditGroup,
    VAuditInvoiceFeedetDetail,
    VAuditInvoiceFeedetGroup,
    VAuditInvoicePayDetail,
    VAuditInvoicePayGroup,
    VAuditPay,
    VCategoryDetail,
    VCategorySurplus,
    VFeedet,
    VFeedetAuditInvoice,
    VInvoice,
    VPay,
    VPreAuditInvoiceFeedet,
    VPreAuditInvoicePay,
    VYewuFeedet,
} from './entities/business.entity';
import {
    UpdateCategoryDto,
    UpdateCurrencyDto,
    UpdateExchangeRateDto,
    UpdateFeeCodDto,
    UpdateFeeCodModDto,
    UpdateFeedetDto,
    UpdateFeeEleDto,
    UpdateFeeEleLovDto,
    UpdateFeeModDto,
    UpdateFeeModEleDto,
    UpdateFeeTypCodDto,
    /* UpdateFeeTypCodDto, */
    UpdateFeeTypDto,
    UpdateInvoiceDetailDto,
    UpdateInvoiceDto,
    UpdatePayDto,
    UpdatePrePayDto,
    UpdateProtocolDto,
    UpdateProtocolRateDto,
} from './dto/update-business.dto';
import { validate } from 'class-validator';
import { plainToInstance } from 'class-transformer';
import {
    difference,
    find, isEmpty,
    isNil, min,
    omit, round
} from 'lodash';
import * as dayjs from 'dayjs';
import * as minMax from 'dayjs/plugin/minMax';
import * as Excel from 'exceljs';
import * as libre from 'libreoffice-convert';
import { v4 as uuidv4 } from 'uuid';
import { Cntr, CntrLease, PortCntrStat } from '@/container/entities/container.entity';
import { CfsStorageRate, Client } from '@/basedata/entities/basedatum.entity';
import { EITZJC, EOQZCC } from '@/common/const/CntrStat';
import { formatDate, formatDateTime, getMonthsBetween } from '@/common/tool/trans';
import { UpdateFeeReportDefineDto } from '@/basedata/dto/update-basedatum.dto';
import { join } from 'node:path';
import { existsSync, mkdirSync, readFileSync, unlinkSync, writeFileSync } from 'node:fs';
import { SysRoleUser } from '@/auth/entities/auth.entity';
import { SysUser } from '@/sys/entities/sys.entity';
type calType = {
    start: dayjs.Dayjs,  // 计费开始时间
    end: dayjs.Dayjs,  // 计费截止时间
    lastCfs?: number,   // 上一个动态的cfs_cod
    lastCfsNam?: string, // 上一个动态的cfs_nam
    currentCfs?: number // 当前动态的cfs_cod
    currentCfsNam?: string, //当前动态的cfs_nam
    cntr_corp_cod?: number, // 客户
    plan_id?: number;
    currency_cod?: string,
    day_rate?: number;
};
type rateType = {
    cfs_cod: number,
    cfs_nam: string,
    cntr_siz_cod: string,
    cntr_typ_cod: string,
    currency_cod: string,
    rate: number,
};
dayjs.extend(minMax);

@Injectable()
export class BusinessService {
    constructor(
        @InjectDataSource() private datasource: DataSource,
        private crud: CrudService,
        @Inject('downloadPath') private readonly downloadpath: string,
    ) { }
    async createProtocol(
        dto: ProtocolDto,
    ): Promise<ResponseJson<Protocol, null>> {
        return await this.crud.create<Protocol, ProtocolDto>(Protocol, dto);
    }
    async updateProtocol(
        dto: UpdateProtocolDto,
    ): Promise<ResponseJson<null, null>> {
        return await this.crud.update<Protocol>(Protocol, dto, {
            protocol_cod: dto.primary_cod,
        });
    }
    async deleteProtocol(dto: DeleteStrPrimaryDto): Promise<ResponseJson> {
        return await this.crud.deleteStrIds<Protocol>(Protocol, dto);
    }
    async queryProtocol(
        query: SqlQueryDto,
    ): Promise<ResponseJson<Protocol[], number>> {
        return await this.crud.query<Protocol>(Protocol, query);
    }

    async createFeeEle(dto: FeeEleDto): Promise<ResponseJson<FeeEle, null>> {
        return await this.crud.create<FeeEle, FeeEleDto>(FeeEle, dto);
    }
    async updateFeeEle(
        dto: UpdateFeeEleDto,
    ): Promise<ResponseJson<null, null>> {
        return await this.crud.update<FeeEle>(FeeEle, dto, {
            fee_ele: dto.primary_cod,
        });
    }
    async deleteFeeEle(dto: DeleteStrPrimaryDto): Promise<ResponseJson> {
        return await this.crud.deleteStrIds<FeeEle>(FeeEle, dto);
    }
    async queryFeeEle(
        query: SqlQueryDto,
    ): Promise<ResponseJson<FeeEle[], number>> {
        return await this.crud.query<FeeEle>(FeeEle, query);
    }
    async createFeeEleLov(
        dto: FeeEleLovDto,
    ): Promise<ResponseJson<FeeEleLov, null>> {
        return await this.crud.create<FeeEleLov, FeeEleLovDto>(FeeEleLov, dto);
    }
    async updateFeeEleLov(
        dto: UpdateFeeEleLovDto,
    ): Promise<ResponseJson<null, null>> {
        return await this.crud.update<FeeEleLov>(FeeEleLov, dto, {
            fee_ele: dto.primary_cod.fee_ele,
            fld_cod: dto.primary_cod.fld_cod,
        });
    }
    async deleteFeeEleLov(dto: DeleteStrPrimaryDto): Promise<ResponseJson> {
        return await this.crud.deleteObjIds<FeeEleLov>(FeeEleLov, dto);
    }
    async queryFeeEleLov(
        query: SqlQueryDto,
    ): Promise<ResponseJson<FeeEleLov[], number>> {
        return await this.crud.query<FeeEleLov>(FeeEleLov, query);
    }
    async initLov(dto: InitLovDto): Promise<ResponseJson> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const sqlStr = await queryRunner.manager
                .createQueryBuilder()
                .select(['e_fld_nam', 'from_table'])
                .from<FeeEle>(FeeEle, 'c_fee_ele')
                .where('fee_ele = :fee_ele', { fee_ele: dto.fee_ele })
                .execute();
            const { e_fld_nam, from_table } = sqlStr[0];
            const queryResult = await queryRunner.query(from_table);
            // console.log(queryResult);

            if (queryResult.length > 0) {
                await queryRunner.manager
                    .createQueryBuilder()
                    .delete()
                    .from(FeeEleLov)
                    .where('fee_ele = :fee_ele', { fee_ele: dto.fee_ele })
                    .execute();
                for (let index = 0; index < queryResult.length; index++) {
                    const element = queryResult[index];
                    await queryRunner.query(`insert into c_fee_ele_lov(fee_ele, fld_cod, e_fld_nam, c_fld_nam, lov_order)
                    values ('${dto.fee_ele}', '${element.cod}', '${e_fld_nam}', '${element.nam}', ${index})
                    `);
                }
            }

            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: [`初始化计算元素值成功`],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }

    async createFeeMod(dto: FeeModDto): Promise<ResponseJson<FeeMod, null>> {
        return await this.crud.create<FeeMod, FeeModDto>(FeeMod, dto);
    }
    async updateFeeMod(
        dto: UpdateFeeModDto,
    ): Promise<ResponseJson<null, null>> {
        return await this.crud.update<FeeMod>(FeeMod, dto, {
            fee_mod_cod: dto.primary_cod,
        });
    }
    async deleteFeeMod(dto: DeleteStrPrimaryDto): Promise<ResponseJson> {
        return await this.crud.deleteStrIds<FeeMod>(FeeMod, dto);
    }
    async queryFeeMod(
        query: SqlQueryDto,
    ): Promise<ResponseJson<FeeMod[], number>> {
        return await this.crud.query<FeeMod>(FeeMod, query);
    }
    async createFeeModEle(
        dto: FeeModEleDto,
    ): Promise<ResponseJson<FeeModEle, null>> {
        return await this.crud.create<FeeModEle, FeeModEleDto>(FeeModEle, dto);
    }
    async updateFeeModEle(
        dto: UpdateFeeModEleDto,
    ): Promise<ResponseJson<null, null>> {
        return await this.crud.update<FeeModEle>(FeeModEle, dto, {
            fee_mod_ele: dto.primary_cod.fee_mod_cod,
            fee_ele: dto.primary_cod.fee_ele,
        });
    }
    async deleteFeeModEle(dto: DeleteStrPrimaryDto): Promise<ResponseJson> {
        return await this.crud.deleteObjIds<FeeModEle>(FeeModEle, dto);
    }
    async queryFeeModEle(
        query: SqlQueryDto,
    ): Promise<ResponseJson<FeeModEle[], number>> {
        return await this.crud.query<FeeModEle>(FeeModEle, query);
    }
    async createFeeCod(dto: FeeCodDto): Promise<ResponseJson<FeeCod, null>> {
        let fee_cod = uuidv4().substring(30);
        let checkExist = await this.datasource.createQueryBuilder<FeeCod>(FeeCod, 'c_fee_cod')
            .select(['count(1) as count'])
            .where('fee_cod = :fee_cod', { fee_cod: fee_cod })
            .getRawOne();

        while (checkExist.count > 0) {
            fee_cod = uuidv4().substring(30);
            checkExist = await this.datasource.createQueryBuilder<FeeCod>(FeeCod, 'c_fee_cod')
                .select(['count(1) as count'])
                .where('fee_cod = :fee_cod', { fee_cod: fee_cod })
                .getRawOne();
        }
        await this.datasource.createQueryBuilder()
            .insert()
            .into<FeeCod>(FeeCod)
            .values({ fee_cod: fee_cod, fee_nam: dto.fee_nam, remark: dto.remark })
            .execute();
        const newData = await this.datasource.createQueryBuilder<FeeCod>(FeeCod, 'c_fee_cod')
            .where('fee_cod = :fee_cod', { fee_cod: fee_cod })
            .getOne();
        return {
            code: ResponseCode.SuccessWithInfo,
            msg: ['增加成功'],
            obj: newData
        }
    }
    async updateFeeCod(
        dto: UpdateFeeCodDto,
    ): Promise<ResponseJson<null, null>> {
        return await this.crud.update<FeeCod>(FeeCod, dto, {
            fee_cod: dto.primary_cod,
        });
    }
    async deleteFeeCod(dto: DeleteStrPrimaryDto): Promise<ResponseJson> {
        return await this.crud.deleteStrIds<FeeCod>(FeeCod, dto);
    }
    async queryFeeCod(
        query: SqlQueryDto,
    ): Promise<ResponseJson<FeeCod[], number>> {
        return await this.crud.query<FeeCod>(FeeCod, query);
    }
    async createFeeTyp(dto: FeeTypDto): Promise<ResponseJson<FeeTyp, null>> {
        return await this.crud.create<FeeTyp, FeeTypDto>(FeeTyp, dto);
    }
    async updateFeeTyp(
        dto: UpdateFeeTypDto,
    ): Promise<ResponseJson<null, null>> {
        return await this.crud.update<FeeTyp>(FeeTyp, dto, {
            fee_typ_cod: dto.primary_cod,
        });
    }
    async deleteFeeTyp(dto: DeleteStrPrimaryDto): Promise<ResponseJson> {
        return await this.crud.deleteStrIds<FeeTyp>(FeeTyp, dto);
    }
    async queryFeeTyp(
        query: SqlQueryDto,
    ): Promise<ResponseJson<FeeTyp[], number>> {
        return await this.crud.query<FeeTyp>(FeeTyp, query);
    }
    async updateFeeTypCod(
        dto: UpdateFeeTypCodDto,
    ): Promise<ResponseJson<null, null>> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const datas = [];
            for (let index = 0; index < dto.feecods.length; index++) {
                const element = dto.feecods[index];
                datas.push({
                    fee_typ_cod: dto.fee_typ,
                    fee_cod: element,
                });
            }
            await queryRunner.manager
                .createQueryBuilder()
                .delete()
                .from(FeeTypCod)
                .where('fee_cod not in (:...cods)', { cods: dto.feecods })
                .andWhere('fee_typ_cod = :feetyp', { feetyp: dto.fee_typ })
                .execute();

            await queryRunner.manager
                .createQueryBuilder()
                .insert()
                .into(FeeTypCod)
                .values(datas)
                .orIgnore()
                .execute();

            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['保存成功'],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async getFeeCodByFeeTyp(
        feeTyp: string,
    ): Promise<ResponseJson<FeeCod[], number>> {
        // 指定feetyp下已有feecod
        const invalids = await this.datasource
            .createQueryBuilder<FeeCod>(FeeCod, 'c_fee_cod')
            .select(['c_fee_cod.fee_cod', 'c_fee_cod.fee_nam'])
            .innerJoin(
                'c_fee_cod.feetypcods',
                'c_fee_typ_cod',
                'c_fee_typ_cod.fee_typ_cod = :typ',
                { typ: feeTyp },
            )
            .getMany();

        // 未分配feecod
        // 一个fee_cod只能属于一个fee_typ-cod
        /* const valids = await this.datasource
            .createQueryBuilder<FeeCod>(FeeCod, 'c_fee_cod')
            .select(['c_fee_cod.fee_cod', 'c_fee_cod.fee_nam'])
            .where((qb) => {
                const subQuery = qb
                    .subQuery()
                    .select('c_fee_typ_cod.fee_cod')
                    .from(FeeTypCod, 'c_fee_typ_cod')
                    .getQuery();
                return 'c_fee_cod.fee_cod not in' + subQuery;
            })
            .getMany(); */
        const valids = await this.datasource
            .createQueryBuilder<FeeCod>(FeeCod, 'c_fee_cod')
            .select(['c_fee_cod.fee_cod', 'c_fee_cod.fee_nam'])
            .where((qb) => {
                const subQuery = qb
                    .subQuery()
                    .select('c_fee_typ_cod.fee_cod')
                    .from(FeeTypCod, 'c_fee_typ_cod')
                    .where('fee_typ_cod = :fee_typ_cod', {
                        fee_typ_cod: feeTyp,
                    })
                    .getQuery();
                return 'c_fee_cod.fee_cod not in' + subQuery;
            })
            .getMany();
        return {
            code: ResponseCode.SuccessNoMsg,
            obj: valids.concat(invalids),
            ext: invalids.length,
        };
    }

    async createFeeCodMod(
        dto: FeeCodModDto,
    ): Promise<ResponseJson<FeeCodMod, null>> {
        return await this.crud.create<FeeCodMod, FeeCodModDto>(FeeCodMod, dto);
    }
    async updateFeeCodMod(
        dto: UpdateFeeCodModDto,
    ): Promise<ResponseJson<null, null>> {
        return await this.crud.update<FeeCodMod>(FeeCodMod, dto, {
            fee_cod: dto.primary_cod.fee_cod,
            fee_mod: dto.primary_cod.fee_mod,
        });
    }
    async deleteFeeCodMod(dto: DeleteStrPrimaryDto): Promise<ResponseJson> {
        return await this.crud.deleteObjIds<FeeCodMod>(FeeCodMod, dto);
    }
    async queryFeeCodMod(
        query: SqlQueryDto,
    ): Promise<ResponseJson<FeeCodMod[], number>> {
        return await this.crud.query<FeeCodMod>(FeeCodMod, query);
    }

    async createProtocolRate(
        dto: ProtocolRatDto,
    ): Promise<ResponseJson<ProtocolRat, null>> {
        return await this.crud.create<ProtocolRat, ProtocolRatDto>(
            ProtocolRat,
            dto,
        );
    }
    async updateProtocolRate(
        dto: UpdateProtocolRateDto,
    ): Promise<ResponseJson<null, null>> {
        return await this.crud.update<ProtocolRat>(ProtocolRat, dto);
    }
    async deleteProtocolRate(dto: DeleteDto): Promise<ResponseJson> {
        return await this.crud.delete<ProtocolRat>(ProtocolRat, dto);
    }
    async queryProtocolRate(
        query: SqlQueryDto,
    ): Promise<ResponseJson<ProtocolRat[], number>> {
        return await this.crud.query<ProtocolRat>(ProtocolRat, query);
    }

    async createCategory(
        dto: CategoryDto,
    ): Promise<ResponseJson<Category, null>> {
        return await this.crud.create<Category, CategoryDto>(Category, dto);
    }
    async updateCategory(
        dto: UpdateCategoryDto,
    ): Promise<ResponseJson<null, null>> {
        return await this.crud.update<Category>(Category, dto);
    }
    async deleteCategory(dto: DeleteDto): Promise<ResponseJson> {
        return await this.crud.delete<Category>(Category, dto);
    }
    async queryCategory(
        query: SqlQueryDto,
    ): Promise<ResponseJson<Category[], number>> {
        return await this.crud.query<Category>(Category, query);
    }
    async queryCategorySurplus(
        query: SqlQueryDto,
    ): Promise<ResponseJson<VCategorySurplus[], number>> {
        return await this.crud.query<VCategorySurplus>(VCategorySurplus, query);
    }
    async createCurrency(
        dto: CurrencyDto,
    ): Promise<ResponseJson<Currency, null>> {
        return await this.crud.create<Currency, CurrencyDto>(Currency, dto);
    }
    async updateCurrency(
        dto: UpdateCurrencyDto,
    ): Promise<ResponseJson<null, null>> {
        return await this.crud.update<Currency>(Currency, dto, {
            currency_cod: dto.primary_cod,
        });
    }
    async deleteCurrency(dto: DeleteStrPrimaryDto): Promise<ResponseJson> {
        return await this.crud.deleteStrIds<Currency>(Currency, dto);
    }
    async queryCurrency(
        query: SqlQueryDto,
    ): Promise<ResponseJson<Currency[], number>> {
        return await this.crud.query<Currency>(Currency, query);
    }
    async createExchangeRate(
        dto: ExchangeRateDto,
    ): Promise<ResponseJson<ExchangeRate, null>> {
        return await this.crud.create<ExchangeRate, ExchangeRateDto>(
            ExchangeRate,
            dto,
        );
    }
    async updateExchangeRate(
        dto: UpdateExchangeRateDto,
    ): Promise<ResponseJson<null, null>> {
        return await this.crud.update<ExchangeRate>(ExchangeRate, dto, {
            from_currency: dto.primary_cod.from_currency,
            to_currency: dto.primary_cod.to_currency,
        });
    }
    async deleteExchangeRate(dto: DeleteStrPrimaryDto): Promise<ResponseJson> {
        return await this.crud.deleteObjIds<ExchangeRate>(ExchangeRate, dto);
    }
    async queryExchangeRate(
        query: SqlQueryDto,
    ): Promise<ResponseJson<ExchangeRate[], number>> {
        return await this.crud.query<ExchangeRate>(ExchangeRate, query);
    }
    async createPay(dto: PayDto, oper_nam: string): Promise<ResponseJson<Pay, null>> {
        dto.fee_nam = oper_nam;
        if (dto.auto_audit === 'Y') {
            const queryRunner = this.datasource.createQueryRunner();
            await queryRunner.connect();
            await queryRunner.startTransaction();
            try {
                dto.audit_finish_id = BooleanType.Y;
                dto.audit_id = BooleanType.Y;
                const insertPayResult = await queryRunner.manager.createQueryBuilder().insert()
                    .into(Pay)
                    .values(omit(dto, ['unaudit_amount', 'unaudit_invoice_amount']))
                    .execute();
                if (insertPayResult.raw['insertId'] > 0) {
                    const feedet = {} as unknown as FeedetDto;
                    feedet.fee_unit_cod = dto.fee_unit_cod;
                    feedet.fee_owner = dto.fee_owner;
                    feedet.fee_typ_cod = dto.fee_typ_cod;
                    feedet.fee_cod = dto.fee_cod;
                    feedet.amount = dto.amount;
                    feedet.currency_cod = dto.currency_cod;
                    feedet.fee_nam = dto.fee_nam;
                    feedet.fee_tim = dto.fee_tim;
                    feedet.fee_in_out = dto.fee_in_out;
                    feedet.audit_id = BooleanType.Y;
                    feedet.audit_finish_id = BooleanType.Y;
                    const feedetid = (await this.createFeedet(feedet, queryRunner)).obj.id;

                    const feeaudit = {} as unknown as FeeAuditDto;
                    feeaudit.pay_id = insertPayResult.raw['insertId'];
                    feeaudit.feedet_id = feedetid;
                    feeaudit.pay_amount = dto.amount;
                    feeaudit.feedet_amount = dto.amount;
                    feeaudit.audit_tim = new Date();
                    feeaudit.audit_no = uuidv4();
                    feeaudit.currency_rate = 1;
                    feeaudit.force_flag = BooleanType.N;
                    await this.createFeeAudit(feeaudit, queryRunner);
                }
                const newPay = await queryRunner.manager.createQueryBuilder<VPay>(VPay, 'v_pay')
                    .where('id = :id', { id: insertPayResult.raw['insertId'] })
                    .getOne();

                await queryRunner.commitTransaction();
                return {
                    code: ResponseCode.SuccessWithInfo,
                    msg: ['保存成功'],
                    obj: newPay
                };
            } catch (err) {
                await queryRunner.rollbackTransaction();
                throw err;
            } finally {
                await queryRunner.release();
            }

        } else {
            return await this.crud.create<Pay, PayDto>(Pay, omit(dto, ['unaudit_amount', 'unaudit_invoice_amount']) as unknown as PayDto);
        }

    }
    async updatePay(dto: UpdatePayDto, oper_nam: string): Promise<ResponseJson<Pay, null>> {

        const data = omit(dto, ['unaudit_amount', 'unaudit_invoice_amount']) as unknown as UpdatePayDto;
        const oldData = await this.datasource.createQueryBuilder<Pay>(Pay, 'pay')
            .where('id = :id', { id: data.id })
            .getOne();
        if (oldData.auto_audit === data.auto_audit) {
            await this.crud.update<Pay>(Pay, data);
            const r = await this.datasource.createQueryBuilder<VPay>(VPay, 'v_pay').where('id = :id', { id: data.id }).getOne();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['保存成功'],
                obj: r
            }
        } else {
            const queryRunner = this.datasource.createQueryRunner();
            await queryRunner.connect();
            await queryRunner.startTransaction()
            try {
                if (data.auto_audit === 'Y') { // 补自动核销
                    const auditCount = await queryRunner.manager.createQueryBuilder()
                        .select(['count(1) as count'])
                        .from<FeeAudit>(FeeAudit, 'fee_audit')
                        .where('pay_id = :pay_id', { pay_id: data.id })
                        .getRawOne();

                    if (auditCount['count'] > 0) {
                        throw new Error('已存在核销记录，不能设置自动核销');
                    }


                    await queryRunner.manager.createQueryBuilder()
                        .update(Pay)
                        .set(omit(data, ['id']))
                        .where('id = :id', { id: data.id })
                        .execute();
                    const sys_tim = new Date();
                    const feedet = {} as unknown as FeedetDto;
                    feedet.fee_unit_cod = dto.fee_unit_cod;
                    feedet.fee_owner = dto.fee_owner;
                    feedet.fee_typ_cod = dto.fee_typ_cod;
                    feedet.fee_cod = dto.fee_cod;
                    feedet.amount = dto.amount;
                    feedet.currency_cod = dto.currency_cod;
                    feedet.fee_nam = oper_nam;
                    feedet.fee_tim = sys_tim;
                    feedet.fee_in_out = dto.fee_in_out;
                    feedet.audit_id = BooleanType.Y;
                    feedet.audit_finish_id = BooleanType.Y;
                    const feedetid = (await this.createFeedet(feedet, queryRunner)).obj.id;

                    const feeaudit = {} as unknown as FeeAuditDto;
                    feeaudit.pay_id = dto.id;
                    feeaudit.feedet_id = feedetid;
                    feeaudit.pay_amount = dto.amount;
                    feeaudit.feedet_amount = dto.amount;
                    feeaudit.audit_tim = sys_tim;
                    feeaudit.audit_no = uuidv4();
                    feeaudit.currency_rate = 1;
                    feeaudit.force_flag = BooleanType.N;
                    await this.createFeeAudit(feeaudit, queryRunner);
                } else {  //撤销自动核销
                    const feeaudits = await queryRunner.manager.createQueryBuilder<FeeAudit>(FeeAudit, 'fee_audit')
                        .where('pay_id = :pay_id', { pay_id: data.id })
                        .getMany();
                    if (feeaudits.length !== 1) {
                        throw new Error('存在多条核销记录，请手动取消核销');
                    }
                    await queryRunner.manager.createQueryBuilder()
                        .delete()
                        .from(FeeAudit)
                        .where('id = :id', { id: feeaudits[0].id })
                        .execute();
                    await queryRunner.manager.createQueryBuilder()
                        .update(Pay)
                        .set({
                            auto_audit: 'N',
                            audit_id: 'N',
                            audit_finish_id: 'N'
                        })
                        .where('id = :id', { id: feeaudits[0].pay_id })
                        .execute();
                    await queryRunner.manager.createQueryBuilder()
                        .update(FeeDet)
                        .set({
                            audit_id: 'N',
                            audit_finish_id: 'N'
                        })
                        .where('id = :id', { id: feeaudits[0].feedet_id })
                        .execute();
                    await queryRunner.manager.createQueryBuilder()
                        .delete()
                        .from(FeeDet)
                        .where('id = :id', { id: feeaudits[0].feedet_id })
                        .execute();

                }
                const updatedPay = await queryRunner.manager.createQueryBuilder<VPay>(VPay, 'v_pay').where('id = :id', { id: data.id }).getOne();
                await queryRunner.commitTransaction();
                return {
                    code: ResponseCode.SuccessWithInfo,
                    msg: ['保存成功'],
                    obj: updatedPay
                }
            } catch (err) {
                await queryRunner.rollbackTransaction();
                throw err;
            } finally {
                await queryRunner.release();
            }
        }
    }
    async deletePay(dto: DeleteDto): Promise<ResponseJson> {
        return await this.crud.delete<Pay>(Pay, dto);
    }
    async queryPay(query: SqlQueryDto): Promise<ResponseJson<Pay[], number>> {
        return await this.crud.query<VPay>(VPay, query);
    }
    async createPrePay(dto: PrePayDto): Promise<ResponseJson<PrePay, null>> {
        return await this.crud.create<PrePay, PrePayDto>(PrePay, dto);
    }
    async updatePrePay(
        dto: UpdatePrePayDto,
    ): Promise<ResponseJson<null, null>> {
        return await this.crud.update<PrePay>(PrePay, dto);
    }
    async deletePrePay(dto: DeleteDto): Promise<ResponseJson> {
        return await this.crud.delete<PrePay>(PrePay, dto);
    }
    async queryPrePay(
        query: SqlQueryDto,
    ): Promise<ResponseJson<PrePay[], number>> {
        return await this.crud.query<PrePay>(PrePay, query);
    }
    async backPrePay(dto: DeleteDto): Promise<ResponseJson> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            for (let index = 0; index < dto.ids.length; index++) {
                const prepayid = dto.ids[index];
                const prepay = await queryRunner.manager
                    .createQueryBuilder<PrePay>(PrePay, 'pre_pay')
                    .where('id = :id', { id: prepayid })
                    .getOne();
                prepay.fee_in_out = prepay.fee_in_out === 'I' ? 'O' : 'I';
                prepay.fee_tim = new Date();
                await queryRunner.manager
                    .createQueryBuilder()
                    .insert()
                    .into(PrePay)
                    .values(omit(prepay, ['id']))
                    .execute();
            }

            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['退款成功'],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async transPrePay(dto: DeleteDto): Promise<ResponseJson> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            for (let index = 0; index < dto.ids.length; index++) {
                const prepayid = dto.ids[index];
                const prepay = await queryRunner.manager
                    .createQueryBuilder<PrePay>(PrePay, 'pre_pay')
                    .where('id = :id', { id: prepayid })
                    .getOne();
                prepay.fee_tim = new Date();
                await queryRunner.manager
                    .createQueryBuilder()
                    .insert()
                    .into(Pay)
                    .values(omit(prepay, ['id']))
                    .execute();
                prepay.fee_in_out = prepay.fee_in_out === 'I' ? 'O' : 'I';
                await queryRunner.manager
                    .createQueryBuilder()
                    .insert()
                    .into(PrePay)
                    .values(omit(prepay, ['id']))
                    .execute();
            }

            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['退款成功'],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async queryVPreAuditInvoicePay(
        query: SqlQueryDto,
    ): Promise<ResponseJson<VPreAuditInvoicePay[], number>> {
        return await this.crud.query<VPreAuditInvoicePay>(
            VPreAuditInvoicePay,
            query,
        );
    }
    async queryVPreAuditInvoiceFeedet(
        query: SqlQueryDto,
    ): Promise<ResponseJson<VPreAuditInvoiceFeedet[], number>> {
        return await this.crud.query<VPreAuditInvoiceFeedet>(
            VPreAuditInvoiceFeedet,
            query,
        );
    }
    async queryAuditPayAndFeedet(
        query: SqlQueryDto,
    ): Promise<ResponseJson<VAuditPay[], VAuditFeedet[]>> {
        const payResult = await this.crud.query<VAuditPay>(VAuditPay, query);
        const feedetResult = await this.crud.query<VAuditFeedet>(
            VAuditFeedet,
            query,
        );
        return {
            code: ResponseCode.SuccessNoMsg,
            obj: payResult.obj,
            ext: feedetResult.obj,
        };
    }
    async createFeedet(
        dto: FeedetDto,
        q?: QueryRunner,
    ): Promise<ResponseJson<FeeDet, null>> {
        const queryRunner = q ?? this.datasource.createQueryRunner();
        const errors = await validate(plainToInstance(FeedetDto, dto));
        if (errors.length > 0) {
            // console.log(dto);

            throw new Error(Object.values(errors[0].constraints).join('   '));
        }
        // console.log('FeeDet', dto);

        return await this.crud.create<FeeDet, FeedetDto>(
            FeeDet,
            dto,
            queryRunner.manager,
        );
    }
    /**
     * 修改/删除 feedet, 业务逻辑检测
     * @param feedet
     * @returns
     */
    checkFeeDet(feedet: FeeDet): { flag: boolean; msg: string } {
        const r = {
            flag: false,
            msg: '',
        };
        if (feedet.audit_finish_id === 'Y' || feedet.audit_id === 'Y') {
            r.msg = '已核销不能修改';
            return r;
        }
        r.flag = true;
        return r;
    }
    async updateFeedet(
        dto: UpdateFeedetDto,
        m?: EntityManager,
    ): Promise<ResponseJson<null, null>> {
        const manager = m ?? this.datasource.createQueryRunner().manager;
        const feedet = await manager
            .createQueryBuilder<FeeDet>(FeeDet, 'fee_det')
            .select()
            .where('id = :id', { id: dto.id })
            .getOne();
        const checkResult = this.checkFeeDet(feedet);
        if (checkResult.flag) {
            // 
            return await this.crud.update<FeeDet>(FeeDet, omit(dto,
                ['cntr', 'cntr_no', 'cntr_owner', 'cntr_siz_cod', 'cntr_typ_cod', 'audit_id', 'audit_finish_id', 'in_truck_sn', 'lease_sn', 'out_truck_sn']),
                null, manager);
        } else {
            throw new Error(checkResult.msg);
        }
    }
    /**
     * 修改箱编号下只有一条相同fee_cod的费用记录
     * @param cntr_no
     * @param dto
     * @returns
     */
    async updateFeedetGroupFeeByCntrNo(
        cntr_no: number,
        dto: UpdateFeedetDto,
        queryRunner: QueryRunner,
    ): Promise<boolean> {
        const feedets = await queryRunner.manager
            .createQueryBuilder<FeeDet>(FeeDet, 'fee_det')
            .select(['id'])
            .where('cntr_no = :cntr_no and fee_cod = :fee_cod', {
                cntr_no: cntr_no,
                fee_cod: dto.fee_cod,
            })
            .execute();

        if (feedets.length > 1) {
            throw new Error(`存在多条费用不能修改`);
        } else if (feedets.length === 1) {
            dto.id = feedets[0].id;
            await this.updateFeedet(dto, queryRunner.manager);
        } else if (feedets.length === 0) {
            await this.createFeedet(
                omit(dto, ['id']) as FeedetDto,
                queryRunner,
            );
        } else {
            throw new Error(`更新费用失败`);
        }
        return true;
    }
    async deleteFeedet(dto: DeleteDto): Promise<ResponseJson> {
        return await this.crud.delete<FeeDet>(FeeDet, dto);
    }
    async queryFeedet(
        query: SqlQueryDto, user: SysUser
    ): Promise<ResponseJson<VFeedet[], number>> {

        const roles = await this.datasource.manager
            .createQueryBuilder<SysRoleUser>(SysRoleUser, 'sys_role_user')
            .select(['roleId'])
            .where('userid = :userid', { userid: user.id })
            .getRawMany();
        let managerAuth = false;
        for (let index = 0; index < roles.length; index++) {
            const element = roles[index];
            if ([1, 3, 11].includes(element.roleId)) {
                managerAuth = true;
                break;
            }
        }

        if (managerAuth) {
            return await this.crud.query<VFeedet>(VFeedet, query);
        } else {
            return await this.crud.query<VYewuFeedet>(VYewuFeedet, query);
        }


    }
    async queryModsByFeeCods(
        query: queryModsByFeeCodDto,
    ): Promise<ResponseJson<FormFieldSelectOption[], null>> {
        const result: Array<FormFieldSelectOption> = [];

        const rMod = await this.datasource
            .createQueryBuilder<FeeMod>(FeeMod, 'c_fee_mod')
            .select(['fee_mod_cod', 'fee_mod_nam'])
            .innerJoin(
                'c_fee_mod.feecodmods',
                'c_fee_cod_mod',
                'c_fee_cod_mod.fee_cod = :fee_cod',
                { fee_cod: `${query.fee_cod}` },
            )
            // .from<FeeMod>(FeeMod, 'c_fee_mod')
            .execute();
        //.getOne();

        rMod.forEach((element) => {
            result.push({
                value: element.fee_mod_cod,
                label: element.fee_mod_nam,
            });
        });
        return {
            code: ResponseCode.SuccessNoMsg,
            obj: result,
        };
    }
    async queryElesByFeeMod(
        query: queryElesByFeeModDto,
    ): Promise<ResponseJson<any[], null>> {
        const result: Array<any> = [];

        const rFeeEle = await this.datasource
            .createQueryBuilder<FeeEle>(FeeEle, 'c_fee_ele')
            .select(['c_fee_ele.fee_ele', 'c_fee_ele.c_fld_nam'])

            .leftJoinAndSelect('c_fee_ele.feeelelovs', 'c_fee_ele_lov')
            .innerJoin(
                'c_fee_ele.feemodeles',
                'c_fee_mod_ele',
                'c_fee_mod_ele.fee_mod_cod = :mod_cod',
                { mod_cod: `${query.fee_mod_cod}` },
            )
            //.execute();
            .getMany();

        rFeeEle.forEach((ele) => {
            const lovs = [];
            ele.feeelelovs.forEach((lov) => {
                lovs.push({
                    value: lov.fld_cod,
                    label: lov.c_fld_nam,
                });
            });
            result.push({
                value: ele.fee_ele,
                label: ele.c_fld_nam,
                lovs,
            });
        });
        return {
            code: ResponseCode.SuccessNoMsg,
            obj: result,
        };
    }
    async insertLeaseFee(calObj: calType, feedetObj: FeedetDto, currency_cod: string, rate: number, q?: QueryRunner) {
        const queryRunner = q ?? this.datasource.createQueryRunner();

        if (isNil(rate)) {
            throw new Error(`计划${calObj.plan_id}没有维护租箱费率`);
        }
        const days = calObj.end.startOf('day').diff(calObj.start.startOf('day'), 'day') + 1;
        if (days > 0 && rate > 0) {
            feedetObj.fee_value = rate;
            feedetObj.fee_ton = days;
            feedetObj.amount = days * rate;
            feedetObj.currency_cod = currency_cod;
            await this.createFeedet(feedetObj, queryRunner);
        }
    }
    async insertStroageFee(calObj: calType, rates: Array<rateType>, feedetObj: FeedetDto, cfs_cod: number, cfs_nam: string, cntr_siz_cod: string, cntr_typ_cod: string, q?: QueryRunner) {
        const queryRunner = q ?? this.datasource.createQueryRunner();
        let rateObj: rateType;
        const findrate = find(rates, (r) => {
            return r.cfs_cod === cfs_cod && r.cntr_siz_cod === cntr_siz_cod
            // && r.cntr_typ_cod === cntr_typ_cod
        });

        if (isNil(findrate)) {
            const dbRate = await queryRunner.manager.createQueryBuilder<CfsStorageRate>(CfsStorageRate, 'c_cfs_storage_rate')
                .select(['rate', 'currency_cod'])
                .where('cfs_cod = :cfs_cod', { cfs_cod })
                .andWhere('cntr_siz_cod = :cntr_siz_cod', { cntr_siz_cod })
                // .andWhere('cntr_typ_cod = :cntr_typ_cod', { cntr_typ_cod })
                .getRawOne();
            if (dbRate) {
                rateObj = {
                    cfs_cod: cfs_cod,
                    cfs_nam: cfs_nam,
                    cntr_siz_cod: cntr_siz_cod,
                    cntr_typ_cod: cntr_typ_cod,
                    currency_cod: dbRate.currency_cod,
                    rate: dbRate.rate
                };
                rates.push(rateObj);
            } else {
                throw new Error(`堆场${cfs_nam}, ${cntr_siz_cod},没有维护堆存费率`);
            }
        } else {
            rateObj = findrate;
        }
        if (isNil(rateObj)) {
            throw new Error(`堆场${cfs_nam}没有维护堆存费率`);
        }
        const days = calObj.end.startOf('day').diff(calObj.start.startOf('day'), 'day') + 1;
        if (days > 0 && rateObj.rate > 0) {
            feedetObj.fee_value = rateObj.rate;
            feedetObj.fee_ton = days;
            feedetObj.amount = days * rateObj.rate;
            feedetObj.currency_cod = rateObj.currency_cod;
            await this.createFeedet(feedetObj, queryRunner);
        }
    }
    /**
     * 生成月结堆存费, 取c_cfs_stroage_rate 费率
     *
     * @param month
     */
    async create0108(
        startTim: Date,
        endTim: Date,
        cntrs: any[],
        operNam: string,
        q?: QueryRunner,
    ): Promise<ResponseJson<null, null>> {
        // console.log('开始计算堆存费');
        // console.log(cntrs);

        const queryRunner = q ?? this.datasource.createQueryRunner();

        /* const cntrs = await queryRunner.manager.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
            .select(['id', 'cntr', 'cntr_siz_cod', 'cntr_typ_cod', 'cntr_owner'])
            .where('to_port_tim <= :endTim', { endTim })
            .andWhere(
                new Brackets((qb) => {
                    qb.where('leav_port_tim >= :startTim', { startTim })
                        .orWhere('leav_port_tim is null')
                }),
            )
            .getRawMany(); */
        const rates: rateType[] = [];
        // 计费状态机
        for (let index = 0; index < cntrs.length; index++) {
            const cntr = cntrs[index];
            const cntrStats = await queryRunner.manager.createQueryBuilder<PortCntrStat>(PortCntrStat, 'port_cntr_stat')
                .select(['port_cntr_stat.current_stat as current_stat', 'port_cntr_stat.cfs_cod as cfs_cod',
                    'port_cntr_stat.stat_tim as stat_tim', 'c_cy_cfs.free_days as free_days', 'c_cy_cfs.cfs_nam_c as cfs_nam',
                    'port_cntr_stat.cal_stack as cal_stack'])
                .leftJoin('port_cntr_stat.Cfs', 'c_cy_cfs')
                .where('cntr_no = :cntr_no', { cntr_no: cntr.id })
                .orderBy('stat_tim', 'ASC')
                .getRawMany();


            const calObj: calType = {
                start: null,
                end: null,
                lastCfs: null,
                lastCfsNam: null,
                currentCfs: null,
                currentCfsNam: null
            };
            // 一个箱子不止一个计费时间段，要根据不同的cfs_Cod进行区分
            for (let statIndex = 0; statIndex < cntrStats.length; statIndex++) {

                const stat = cntrStats[statIndex];
                calObj.lastCfs = calObj.currentCfs;
                calObj.lastCfsNam = calObj.currentCfsNam;
                calObj.currentCfs = stat.cfs_cod;
                calObj.currentCfsNam = stat.cfs_nam;
                if (stat.current_stat === '2' && stat.cal_stack === 'Y') { // 在场且开始计算堆存费
                    /* if ((!isNil(calObj.lastCfs)) && calObj.lastCfs !== calObj.currentCfs) {
                        throw new Error(`箱${cntr.cntr}动态错误`);
                    } */
                    if (calObj.lastCfs !== calObj.currentCfs) {  // 计时开始
                        calObj.start = dayjs(stat.stat_tim).add(stat.free_days ?? 0, 'day')  // 加免费天数
                    }
                    if (dayjs(stat.stat_tim).isAfter(dayjs(endTim))) {  // 动态时间大于统计截止时间
                        // calObj.end = dayjs(endTim);
                        break;
                    }
                }
                if (stat.current_stat !== '2' || stat.cal_stack === 'N') {   // 计时截止
                    // calObj.end = dayjs.min(dayjs(stat.stat_tim), dayjs(endTim));
                    if (dayjs(stat.stat_tim).isBefore(dayjs(startTim))) { // 动态时间早于统计开始时间，在统计开始时间前已经离场
                        calObj.start = null;
                        calObj.end = null;
                    } else {

                        calObj.end = dayjs.min(dayjs(stat.stat_tim), dayjs(endTim));

                        if (!isNil(calObj.start)) {  // 排除场外多次动态的情况
                            //计算费用
                            calObj.start = dayjs.max(calObj.start, dayjs(startTim));
                            // const days = calObj.start.startOf('day').diff(calObj.end.startOf('day'), 'day');
                            // console.log(`箱号${cntr.cntr}站点${calObj.lastCfs}天数${days}`);'
                            const feedetObj = {} as unknown as FeedetDto;
                            feedetObj.fee_unit_cod = calObj.lastCfs;
                            feedetObj.cntr_no = cntr.id;
                            feedetObj.fee_owner = cntr.cntr_owner;
                            feedetObj.fee_typ_cod = '02';
                            feedetObj.fee_cod = '0108';
                            feedetObj.fee_nam = operNam;
                            feedetObj.fee_tim = calObj.end.startOf('second').toDate();


                            feedetObj.pay_way_id = '1'; // 系统生成
                            feedetObj.fee_in_out = 'O';
                            await this.insertStroageFee(calObj, rates, feedetObj, calObj.lastCfs, calObj.lastCfsNam, cntr.cntr_siz_cod, cntr.cntr_typ_cod, queryRunner);
                            calObj.end = null;
                        }
                        calObj.start = null;
                    }
                }
            }

            if (isNil(calObj.end)) {
                calObj.end = dayjs(endTim);
            }
            if (isNil(calObj.lastCfs)) {
                calObj.lastCfs = calObj.currentCfs;
                calObj.lastCfsNam = calObj.currentCfsNam;
            }
            /*if (isNil(calObj.lastCfs)) {
                throw new Error(`箱${cntr.cntr}计费站点为空`);
            } */
            if (!isNil(calObj.start)) {
                calObj.start = dayjs.max(calObj.start, dayjs(startTim));
                // 计算费用

                const feedetObj = {} as unknown as FeedetDto;
                feedetObj.fee_unit_cod = calObj.lastCfs;
                feedetObj.cntr_no = cntr.id;
                feedetObj.fee_owner = cntr.cntr_owner;
                feedetObj.fee_typ_cod = '02';
                feedetObj.fee_cod = '0108';
                feedetObj.fee_nam = operNam;
                feedetObj.fee_tim = calObj.end.startOf('second').toDate();
                feedetObj.pay_way_id = '1'; // 系统生成
                feedetObj.fee_in_out = 'O';

                await this.insertStroageFee(calObj, rates, feedetObj, calObj.lastCfs, calObj.lastCfsNam, cntr.cntr_siz_cod, cntr.cntr_typ_cod, queryRunner);
                // const days = calObj.end.startOf('day').diff(calObj.start.startOf('day'), 'day');
                // console.log(`箱号${cntr.cntr}站点$${calObj.lastCfs}天数${days}`);
            }
        }
        /* if (days > 0) {
            const feedetObj = {} as unknown as FeedetDto;
            feedetObj.fee_unit_cod = cntr.inplan.fees[0].fee_unit_cod;
            feedetObj.cntr_no = cntr.id;
            feedetObj.fee_owner = cntr.inplan.fees[0].fee_owner;
            feedetObj.fee_typ_cod = cntr.inplan.fees[0].fee_typ_cod;
            feedetObj.fee_cod = cntr.inplan.fees[0].fee_cod;
            feedetObj.fee_value = cntr.inplan.fees[0].amount;
            feedetObj.fee_ton = days;
            feedetObj.amount = days * cntr.inplan.fees[0].amount;
            feedetObj.currency_cod = cntr.inplan.fees[0].currency_cod;
            feedetObj.fee_nam = operNam;
            feedetObj.fee_tim = calEndTim.startOf('day').toDate();
            feedetObj.pay_way_id = '1'; // 系统生成
            feedetObj.fee_in_out = 'O';
            await this.createFeedet(feedetObj, queryRunner);
        } */

        return {
            code: ResponseCode.SuccessNoMsg,
        };
    }
    /**
     * 计算月结租箱费
     * @param startTim 
     * @param endTim 
     * @param operNam 
     * @param q 
     * @returns 
     */
    async create0105(
        startTim: Date,
        endTim: Date,
        cntrs: any[],
        operNam: string,
        q?: QueryRunner,
    ): Promise<ResponseJson<null, null>> {
        const queryRunner = q ?? this.datasource.createQueryRunner();

        // 检查是否已生成当月租赁费
        /* const leaseCntrs = await queryRunner.manager
            .createQueryBuilder<CntrLease>(CntrLease, 'cntr_lease')
            .leftJoinAndSelect('cntr_lease.leaseplan', 'v_truck_cntr_leaseplan')
            .leftJoinAndSelect(
                'v_truck_cntr_leaseplan.fees',
                'truck_cntr_plan_fee',
                "truck_cntr_plan_fee.fee_cod = '0105'",
            )
            .leftJoinAndSelect('cntr_lease.cntr', 'cntr_file')
            .where('out_date <= :endTim', { endTim })
            .andWhere('back_date >= :startTim', { startTim })
            .orWhere('back_date is null')
            .getMany(); */
        // console.log('cntrs', cntrs);

        for (let index = 0; index < cntrs.length; index++) {
            const cntr = cntrs[index];
            const cntrStats = await queryRunner.manager.createQueryBuilder<PortCntrStat>(PortCntrStat, 'port_cntr_stat')
                .select(['port_cntr_stat.current_stat as current_stat', 'port_cntr_stat.cntr_stat_cod as cntr_stat_cod',
                    'port_cntr_stat.stat_tim as stat_tim', 'truck_cntr_plan.free_days as free_days',
                    'truck_cntr_plan.day_rate as day_rate', 'truck_cntr_plan.cntr_corp_cod as cntr_corp_cod',
                    'truck_cntr_plan.currency_cod as currency_cod', 'truck_cntr_plan.id as plan_id'])
                .leftJoin('port_cntr_stat.plan', 'truck_cntr_plan')
                .leftJoin(CntrLease, 'cntr_lease', 'cntr_lease.lease_sn = port_cntr_stat.plan_id and cntr_lease.cntr_no = port_cntr_stat.cntr_no ')
                .addSelect(['cntr_lease.day_rate as spec_day_rate', 'cntr_lease.currency_cod as spec_currency_cod'])
                .where('port_cntr_stat.cntr_no = :cntr_no', { cntr_no: cntr.id })
                .andWhere(
                    new Brackets((qb) => {
                        qb.where('port_cntr_stat.cntr_stat_cod = :stat_cod1', { stat_cod1: EOQZCC })
                            .orWhere('port_cntr_stat.cntr_stat_cod = :stat_cod2', { stat_cod2: EITZJC })
                    }),
                )
                .orderBy('port_cntr_stat.stat_tim', 'ASC')
                .getRawMany();
            
            /* const cntrStats1 = await queryRunner.manager.createQueryBuilder<PortCntrStat>(PortCntrStat, 'port_cntr_stat')
                .select(['port_cntr_stat.current_stat as current_stat', 'port_cntr_stat.cntr_stat_cod as cntr_stat_cod',
                    'port_cntr_stat.stat_tim as stat_tim', 'truck_cntr_plan.free_days as free_days',
                    'truck_cntr_plan.day_rate as day_rate', 'truck_cntr_plan.cntr_corp_cod as cntr_corp_cod',
                    'truck_cntr_plan.currency_cod as currency_cod', 'truck_cntr_plan.id as plan_id'])
                .leftJoin('port_cntr_stat.plan', 'truck_cntr_plan')
                
                .where('cntr_no = :cntr_no', { cntr_no: cntr.id })
                .andWhere(
                    new Brackets((qb) => {
                        qb.where('cntr_stat_cod = :stat_cod1', { stat_cod1: EOQZCC })
                            .orWhere('cntr_stat_cod = :stat_cod2', { stat_cod2: EITZJC })
                    }),
                )
                .orderBy('stat_tim', 'ASC')
                .getRawMany(); 
            
            if (cntrStats.length !== cntrStats1.length ) {
                console.log('不相符', cntr.cntr);
            }*/
            const calObj: calType = {
                start: null,
                end: null,
                cntr_corp_cod: null,
                plan_id: null,
            };
            for (let statIndex = 0; statIndex < cntrStats.length; statIndex++) {
                const stat = cntrStats[statIndex];
                if (stat.current_stat === '1') {
                    calObj.start = dayjs(stat.stat_tim).add(stat.free_days ?? 0, 'day');  // 加免费天数
                    calObj.cntr_corp_cod = stat.cntr_corp_cod;
                    calObj.currency_cod = stat.spec_currency_cod ?? stat.currency_cod;
                    calObj.day_rate = stat.spec_day_rate ?? stat.day_rate;
                    calObj.plan_id = stat.plan_id;
                    if (dayjs(stat.stat_tim).isAfter(dayjs(endTim))) {  // 动态时间大于统计截止时间
                        break;
                    }
                }
                if (stat.current_stat === '2') {
                    if (dayjs(stat.stat_tim).isBefore(dayjs(startTim))) { // 动态时间早于统计开始时间，在统计开始时间前已经返场
                        calObj.start = null;
                        calObj.end = null;
                    } else {
                        calObj.end = dayjs.min(dayjs(stat.stat_tim), dayjs(endTim));
                        if (!isNil(calObj.start)) {
                            //计算费用
                            calObj.start = dayjs.max(calObj.start, dayjs(startTim));
                            // const days = calObj.start.startOf('day').diff(calObj.end.startOf('day'), 'day');
                            // console.log(`箱号${cntr.cntr}站点${calObj.lastCfs}天数${days}`);'
                            const feedetObj = {} as unknown as FeedetDto;
                            feedetObj.fee_unit_cod = calObj.cntr_corp_cod;
                            feedetObj.cntr_no = cntr.id;
                            feedetObj.fee_owner = cntr.cntr_owner;
                            feedetObj.fee_typ_cod = '03';
                            feedetObj.fee_cod = '0105';
                            feedetObj.fee_nam = operNam;
                            feedetObj.fee_tim = calObj.end.startOf('second').toDate();
                            feedetObj.pay_way_id = '1'; // 系统生成
                            feedetObj.fee_in_out = 'I';
                            feedetObj.lease_sn = calObj.plan_id;
                            await this.insertLeaseFee(calObj, feedetObj, calObj.currency_cod, calObj.day_rate, queryRunner);
                            calObj.end = null;
                        }
                        calObj.start = null;
                    }
                }
            }
            if (isNil(calObj.end)) {
                calObj.end = dayjs(endTim);
            }
            if (!isNil(calObj.start)) {
                calObj.start = dayjs.max(calObj.start, dayjs(startTim));
                // 计算费用

                const feedetObj = {} as unknown as FeedetDto;
                feedetObj.fee_unit_cod = calObj.cntr_corp_cod;
                feedetObj.cntr_no = cntr.id;
                feedetObj.fee_owner = cntr.cntr_owner;
                feedetObj.fee_typ_cod = '03';
                feedetObj.fee_cod = '0105';
                feedetObj.fee_nam = operNam;
                feedetObj.fee_tim = calObj.end.startOf('second').toDate();
                feedetObj.pay_way_id = '1'; // 系统生成
                feedetObj.fee_in_out = 'I';
                feedetObj.lease_sn = calObj.plan_id;
                await this.insertLeaseFee(calObj, feedetObj, calObj.currency_cod, calObj.day_rate, queryRunner);
            }
        }
        return {
            code: ResponseCode.SuccessNoMsg,
        };
    }
    async createMonthFee(
        month: Date,
        operNam: string,
    ): Promise<ResponseJson<null, null>> {
        const startTim = dayjs(month).startOf('month');
        const endTim = dayjs(month).endOf('month');
        if (dayjs().isBefore(endTim)) {
            throw new Error('不能生成晚于当前日期费用');
        }

        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const checkCount = await queryRunner.manager
                .createQueryBuilder<FeeDet>(FeeDet, 'fee_det')
                .where('fee_tim >= :startTim', { startTim: startTim.toDate() })
                .andWhere('fee_tim <= :endTim', { endTim: endTim.toDate() })
                .andWhere("pay_way_id = '1' ")
                .getCount();
            if (checkCount > 0) {
                throw new Error('已生成当月费用');
            }
            const cntrs = await queryRunner.manager.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
                .select(['id', 'cntr', 'cntr_siz_cod', 'cntr_typ_cod', 'cntr_owner'])
                .where('to_port_tim <= :endTim', { endTim: endTim.startOf('second').toDate() })
                .andWhere(
                    new Brackets((qb) => {
                        qb.where('leav_port_tim >= :startTim', { startTim: startTim.startOf('second').toDate() })
                            .orWhere('leav_port_tim is null')
                    }),
                )
                .getRawMany();

            // 0108 堆存费
            const result0108 = await this.create0108(
                startTim.toDate(),
                endTim.toDate(),
                cntrs,
                operNam,
                queryRunner,
            );
            // 0105 租赁费
            const result0105 = await this.create0105(
                startTim.toDate(),
                endTim.toDate(),
                cntrs,
                operNam,
                queryRunner,
            );
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['生成成功'],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async cancelMonthFee(month: Date): Promise<ResponseJson<null, null>> {
        const startTim = dayjs(month).startOf('month');
        const endTim = dayjs(month).endOf('month');

        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const deleteResult = await queryRunner.manager
                .createQueryBuilder()
                .delete()
                .from(FeeDet)
                .where('fee_tim >= :startTim', { startTim: startTim.toDate() })
                .andWhere('fee_tim <= :endTim', { endTim: endTim.toDate() })
                .andWhere("pay_way_id = '1' ")
                .execute();

            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['删除成功'],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async createFeeAudit(
        dto: FeeAuditDto,
        q?: QueryRunner,
    ): Promise<ResponseJson<FeeAudit, null>> {
        const queryRunner = q ?? this.datasource.createQueryRunner();
        const errors = await validate(plainToInstance(FeeAuditDto, dto));
        if (errors.length > 0) {
            // console.log(dto);

            throw new Error(Object.values(errors[0].constraints).join('   '));
        }
        // console.log('FeeDet', dto);

        return await this.crud.create<FeeAudit, FeeAuditDto>(
            FeeAudit,
            dto,
            queryRunner.manager,
        );
    }
    async createInvoicePayAudit(
        dto: InvoicePayAuditDto,
        q?: QueryRunner,
    ): Promise<ResponseJson<InvoiceAuditPay, null>> {
        const queryRunner = q ?? this.datasource.createQueryRunner();
        const errors = await validate(plainToInstance(InvoicePayAuditDto, dto));
        if (errors.length > 0) {
            // console.log(dto);

            throw new Error(Object.values(errors[0].constraints).join('   '));
        }
        // console.log('FeeDet', dto);

        return await this.crud.create<InvoiceAuditPay, InvoicePayAuditDto>(
            InvoiceAuditPay,
            dto,
            queryRunner.manager,
        );
    }
    async createInvoiceFeedetAudit(
        dto: InvoiceFeedetAuditDto,
        q?: QueryRunner,
    ): Promise<ResponseJson<InvoiceAuditFeedet, null>> {
        const queryRunner = q ?? this.datasource.createQueryRunner();
        const errors = await validate(
            plainToInstance(InvoiceFeedetAuditDto, dto),
        );
        if (errors.length > 0) {
            // console.log(dto);

            throw new Error(Object.values(errors[0].constraints).join('   '));
        }
        // console.log('FeeDet', dto);

        return await this.crud.create<
            InvoiceAuditFeedet,
            InvoiceFeedetAuditDto
        >(InvoiceAuditFeedet, dto, queryRunner.manager);
    }
    async audit(auditobj: AuditDto): Promise<ResponseJson<any, null>> {
        if (auditobj.force_audit === 'Y' && isNil(auditobj.audit_remark)) {
            throw new Error('强制核销必须输入备注');
        }
        const auditNo = uuidv4();
        const sysDate = new Date();

        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const feedets = await queryRunner.manager
                .createQueryBuilder<VAuditFeedet>(
                    VAuditFeedet,
                    'v_audit_feedet',
                )
                .where('id in (:...ids)', { ids: auditobj.feedetids })
                .getMany();

            const pays = await queryRunner.manager
                .createQueryBuilder<VAuditPay>(VAuditPay, 'v_audit_pay')
                .where('id in (:...ids)', { ids: auditobj.payids })
                .getMany();

            // 第一次循环 处理相同货币
            for (let payIndex = pays.length - 1; payIndex >= 0; payIndex--) {
                const pay = pays[payIndex];

                if (feedets.length === 0) {
                    break;
                }
                if (auditobj.fee_unit_cod !== pay.fee_unit_cod) {
                    throw new Error('存在客户与查询条件不一致');
                }
                if (auditobj.fee_owner !== pay.fee_owner) {
                    throw new Error('存在成本中心与查询条件不一致');
                }
                if (auditobj.fee_in_out !== pay.fee_in_out) {
                    throw new Error('存在收付款与查询条件不一致');
                }
                if (pay.unaudit_amount <= 0) {
                    throw new Error('存在错误的未核销金额');
                }
                // 循环feedets
                for (
                    let feedetIndex = feedets.length - 1;
                    feedetIndex >= 0;
                    feedetIndex--
                ) {
                    const feedet = feedets[feedetIndex];
                    if (auditobj.fee_unit_cod !== feedet.fee_unit_cod) {
                        throw new Error('存在客户与查询条件不一致');
                    }
                    if (auditobj.fee_owner !== feedet.fee_owner) {
                        throw new Error('存在成本中心与查询条件不一致');
                    }
                    if (auditobj.fee_in_out !== feedet.fee_in_out) {
                        throw new Error('存在收付款与查询条件不一致');
                    }
                    if (pay.unaudit_amount <= 0) {
                        throw new Error('存在错误的未核销金额');
                    }
                    if (pay.currency_cod === feedet.currency_cod) {
                        const auditAmount = min([
                            pay.unaudit_amount,
                            feedet.unaudit_amount,
                        ]);

                        const audit = {} as unknown as FeeAuditDto;
                        audit.pay_id = pay.id;
                        audit.feedet_id = feedet.id;
                        audit.pay_amount = auditAmount;
                        audit.feedet_amount = auditAmount;
                        audit.currency_rate = 1;
                        audit.audit_no = auditNo;
                        audit.audit_tim = sysDate;
                        audit.force_flag = BooleanType.N;
                        await this.createFeeAudit(audit, queryRunner);
                        pay.unaudit_amount = pay.unaudit_amount - auditAmount;
                        feedet.unaudit_amount =
                            feedet.unaudit_amount - auditAmount;
                        if (feedet.unaudit_amount <= 0) {
                            await queryRunner.manager
                                .createQueryBuilder()
                                .update(FeeDet)
                                .set({ audit_id: 'Y', audit_finish_id: 'Y' })
                                .where('id = :id', { id: feedet.id })
                                .execute();
                            feedets.splice(feedetIndex, 1);
                        } else {
                            await queryRunner.manager
                                .createQueryBuilder()
                                .update(FeeDet)
                                .set({ audit_id: 'Y' })
                                .where('id = :id', { id: feedet.id })
                                .execute();
                        }
                        if (pay.unaudit_amount <= 0) {
                            await queryRunner.manager
                                .createQueryBuilder()
                                .update(Pay)
                                .set({ audit_id: 'Y', audit_finish_id: 'Y' })
                                .where('id = :id', { id: pay.id })
                                .execute();
                            pays.splice(payIndex, 1);
                            break;
                        } else {
                            await queryRunner.manager
                                .createQueryBuilder()
                                .update(Pay)
                                .set({ audit_id: 'Y' })
                                .where('id = :id', { id: pay.id })
                                .execute();
                        }
                    }
                }
            }
            // 第二次循环处理不同货币
            for (let payIndex = pays.length - 1; payIndex >= 0; payIndex--) {
                const pay = pays[payIndex];

                if (feedets.length === 0) {
                    break;
                }
                // 循环feedets
                for (
                    let feedetIndex = feedets.length - 1;
                    feedetIndex >= 0;
                    feedetIndex--
                ) {
                    const feedet = feedets[feedetIndex];
                    if (auditobj.fee_unit_cod !== feedet.fee_unit_cod) {
                        throw new Error('存在客户与查询条件不一致');
                    }
                    if (auditobj.fee_owner !== feedet.fee_owner) {
                        throw new Error('存在成本中心与查询条件不一致');
                    }
                    if (auditobj.fee_in_out !== feedet.fee_in_out) {
                        throw new Error('存在收付款与查询条件不一致');
                    }
                    if (pay.unaudit_amount <= 0) {
                        throw new Error('存在错误的未核销金额');
                    }
                    if (pay.currency_cod === feedet.currency_cod) {
                        continue;
                    }
                    /* const exchangeRate = await queryRunner.manager
                        .createQueryBuilder<ExchangeRate>(
                            ExchangeRate,
                            'c_exchange_rate',
                        )
                        .where('from_currency = :from_currency', {
                            from_currency: pay.currency_cod,
                        })
                        .andWhere('to_currency = :to_currency', {
                            to_currency: feedet.currency_cod,
                        })
                        .getOne(); */
                    const exchangeRate = await this.getRate(pay.currency_cod, feedet.currency_cod, queryRunner);
                    if (!exchangeRate) {
                        throw new Error(
                            `没有${pay.currency_cod}到${feedet.currency_cod}的汇率`,
                        );
                    }
                    //
                    const auditAmount = min([
                        round(pay.unaudit_amount * exchangeRate, 2),
                        feedet.unaudit_amount,
                    ]);

                    const audit = {} as unknown as FeeAuditDto;
                    audit.pay_id = pay.id;
                    audit.feedet_id = feedet.id;
                    audit.pay_amount = round(
                        auditAmount / exchangeRate,
                        2,
                    );
                    audit.feedet_amount = auditAmount;
                    audit.currency_rate = exchangeRate;
                    audit.audit_no = auditNo;
                    audit.audit_tim = sysDate;
                    audit.force_flag = BooleanType.N;
                    await this.createFeeAudit(audit, queryRunner);
                    pay.unaudit_amount = pay.unaudit_amount - audit.pay_amount;
                    feedet.unaudit_amount =
                        feedet.unaudit_amount - audit.feedet_amount;

                    if (feedet.unaudit_amount <= 0) {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(FeeDet)
                            .set({ audit_id: 'Y', audit_finish_id: 'Y' })
                            .where('id = :id', { id: feedet.id })
                            .execute();
                        feedets.splice(feedetIndex, 1);
                    } else {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(FeeDet)
                            .set({ audit_id: 'Y' })
                            .where('id = :id', { id: feedet.id })
                            .execute();
                    }
                    if (pay.unaudit_amount <= 0) {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(Pay)
                            .set({ audit_id: 'Y', audit_finish_id: 'Y' })
                            .where('id = :id', { id: pay.id })
                            .execute();
                        pays.splice(payIndex, 1);
                        break;
                    } else {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(Pay)
                            .set({ audit_id: 'Y' })
                            .where('id = :id', { id: pay.id })
                            .execute();
                    }
                }
            }
            // 第三次循环处理强制核销
            if (auditobj.force_audit === 'Y') {
                for (let payIndex = 0; payIndex < pays.length; payIndex++) {
                    const pay = pays[payIndex];
                    const audit = {} as unknown as FeeAuditDto;
                    audit.pay_id = pay.id;
                    // audit.feedet_id = feedet.id;
                    audit.pay_amount = pay.unaudit_amount;
                    // audit.feedet_amount = auditAmount;
                    // audit.currency_rate = 1;
                    audit.audit_no = auditNo;
                    audit.audit_tim = sysDate;
                    audit.force_flag = BooleanType.Y;
                    audit.remark = auditobj.audit_remark;
                    await this.createFeeAudit(audit, queryRunner);
                    await queryRunner.manager
                        .createQueryBuilder()
                        .update(Pay)
                        .set({ audit_id: 'Y', audit_finish_id: 'Y' })
                        .where('id = :id', { id: pay.id })
                        .execute();
                }
                for (
                    let feedetIndex = 0;
                    feedetIndex < feedets.length;
                    feedetIndex++
                ) {
                    const feedet = feedets[feedetIndex];
                    const audit = {} as unknown as FeeAuditDto;
                    audit.feedet_id = feedet.id;
                    audit.feedet_amount = feedet.unaudit_amount;
                    audit.audit_no = auditNo;
                    audit.audit_tim = sysDate;
                    audit.force_flag = BooleanType.Y;
                    audit.remark = auditobj.audit_remark;
                    await this.createFeeAudit(audit, queryRunner);
                    await queryRunner.manager
                        .createQueryBuilder()
                        .update(FeeDet)
                        .set({ audit_id: 'Y', audit_finish_id: 'Y' })
                        .where('id = :id', { id: feedet.id })
                        .execute();
                }
            }
            await queryRunner.commitTransaction();
            const auditPayResult = await this.datasource
                .createQueryBuilder<VAudit>(VAudit, 'v_audit')
                .select([
                    'v_audit.pay_currency  as currency',
                    'SUM(v_audit.pay_amount) as sum',
                ])
                .where('audit_no = :audit_no', { audit_no: auditNo })
                .andWhere('pay_id is not null')
                .groupBy('v_audit.pay_currency')
                .getRawMany();
            // console.log(auditPayResult);

            const auditFeedetResult = await this.datasource
                .createQueryBuilder<VAudit>(VAudit, 'v_audit')
                .select([
                    'v_audit.feedet_currency as currency',
                    'SUM(v_audit.feedet_amount) as sum',
                ])
                .where('audit_no = :audit_no', { audit_no: auditNo })
                .andWhere('feedet_id is not null')
                .groupBy('v_audit.feedet_currency')
                .getRawMany();

            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['核销成功'],
                obj: {
                    pay: auditPayResult,
                    feedet: auditFeedetResult,
                },
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async queryAuditGroup(
        query: SqlQueryDto,
    ): Promise<ResponseJson<VAuditGroup[], number>> {
        return await this.crud.query<VAuditGroup>(VAuditGroup, query);
    }
    async queryAuditDetail(
        query: SqlQueryDto,
    ): Promise<ResponseJson<VAudit[], number>> {
        return await this.crud.query<VAudit>(VAudit, query);
    }
    async cancelAudit(
        dto: DeleteStrPrimaryDto,
    ): Promise<ResponseJson<null, null>> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const pays = await queryRunner.manager
                .createQueryBuilder<FeeAudit>(FeeAudit, 'fee_audit')
                .select('DISTINCT fee_audit.pay_id')
                .where('audit_no in (:...audit_no)', { audit_no: dto.ids })
                .getRawMany();
            const feedets = await queryRunner.manager
                .createQueryBuilder<FeeAudit>(FeeAudit, 'fee_audit')
                .select('DISTINCT fee_audit.feedet_id')
                .where('audit_no in (:...audit_no)', { audit_no: dto.ids })
                .getRawMany();
            // console.log(pays);
            const payids = [];
            const feedetids = [];
            for (let payIndex = 0; payIndex < pays.length; payIndex++) {
                payids.push(pays[payIndex]['pay_id']);
            }
            for (
                let feedetIndex = 0;
                feedetIndex < feedets.length;
                feedetIndex++
            ) {
                feedetids.push(feedets[feedetIndex]['feedet_id']);
            }

            await queryRunner.manager
                .createQueryBuilder()
                .delete()
                .from(FeeAudit)
                .where('audit_no in (:...audit_no)', { audit_no: dto.ids })
                .execute();

            await queryRunner.manager
                .createQueryBuilder()
                .update(Pay)
                .set({ audit_finish_id: 'N' })
                .where('id in (:...ids)', { ids: payids })
                .execute();
            await queryRunner.manager
                .createQueryBuilder()
                .update(FeeDet)
                .set({ audit_finish_id: 'N' })
                .where('id in (:...ids)', { ids: feedetids })
                .execute();

            const diffPays = await queryRunner.manager
                .createQueryBuilder<FeeAudit>(FeeAudit, 'fee_audit')
                .select('DISTINCT fee_audit.pay_id')
                .where('pay_id in (:...ids)', { ids: payids })
                .getRawMany();
            const diffPayids = [];
            for (let payIndex = 0; payIndex < diffPays.length; payIndex++) {
                diffPayids.push(diffPays[payIndex]['pay_id']);
            }
            await queryRunner.manager
                .createQueryBuilder()
                .update(Pay)
                .set({ audit_id: 'N' })
                .where('id in (:...ids)', {
                    ids: difference(payids, diffPayids),
                })
                .execute();

            const diffFeedets = await queryRunner.manager
                .createQueryBuilder<FeeAudit>(FeeAudit, 'fee_audit')
                .select('DISTINCT fee_audit.feedet_id')
                .where('feedet_id in (:...ids)', { ids: feedetids })
                .getRawMany();
            const diffFeedetids = [];
            for (
                let feedetIndex = 0;
                feedetIndex < diffFeedets.length;
                feedetIndex++
            ) {
                diffFeedetids.push(diffFeedets[feedetIndex]['feedet_id']);
            }
            await queryRunner.manager
                .createQueryBuilder()
                .update(FeeDet)
                .set({ audit_id: 'N' })
                .where('id in (:...ids)', {
                    ids: difference(feedetids, diffFeedetids),
                })
                .execute();

            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['撤销成功'],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async createInvoice(dto: InvoiceDto): Promise<ResponseJson<Invoice, null>> {
        const errors = await validate(plainToInstance(InvoiceDto, dto));
        if (errors.length > 0) {
            throw new Error(Object.values(errors[0].constraints).join('   '));
        }
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        let insertInvoiceResult: InsertResult = undefined;
        try {
            insertInvoiceResult = await queryRunner.manager
                .createQueryBuilder()
                .insert()
                .into(Invoice)
                .values(omit(dto, ['details']))
                .execute();
            if (insertInvoiceResult.raw.affectedRows === 1) {
                const invoice_id = insertInvoiceResult.raw.insertId;
                if (dto.details && dto.details.length > 0) {
                    for (let index = 0; index < dto.details.length; index++) {
                        const detail = dto.details[index];
                        detail.invoice_id = invoice_id;
                        const errors = await validate(
                            plainToInstance(InvoiceDetailDto, detail),
                        );
                        if (errors.length > 0) {
                            throw new Error(
                                Object.values(errors[0].constraints).join(
                                    '   ',
                                ),
                            );
                        }
                    }

                    await queryRunner.manager
                        .createQueryBuilder()
                        .insert()
                        .into(InvoiceDetail)
                        .values(dto.details)
                        .execute();
                }
            }
            await queryRunner.commitTransaction();
        } catch (err) {
            await queryRunner.rollbackTransaction();
            console.log(err);

            throw err;
        } finally {
            await queryRunner.release();
        }
        const invoice = await this.datasource
            .createQueryBuilder<Invoice>(Invoice, 'invoice')
            .leftJoinAndSelect('invoice.details', 'invoice_detail')
            .where('invoice.id = :id', { id: insertInvoiceResult.raw.insertId })
            .getOne();

        return {
            code: ResponseCode.SuccessWithInfo,
            msg: ['保存成功'],
            obj: invoice,
        };
    }
    async updateInvoice(
        dto: UpdateInvoiceDto,
    ): Promise<ResponseJson<Invoice, null>> {
        /* const updateDto = omit(dto, [
            'unaudit_pay_amount',
            'unaudit_feedet_amount',
        ]); */
        const queryRunner = await this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction()
        try {
            const updateResult = await queryRunner.manager.createQueryBuilder()
                .update(Invoice)
                .set(omit(dto, ['id', 'details', 'unaudit_pay_amount', 'unaudit_feedet_amount']))
                .where('id = :id', { id: dto.id })
                .execute();
            const ids = [];
            if (dto.details.length) {

                for (let index = 0; index < dto.details.length; index++) {
                    const detail = dto.details[index] as unknown as UpdateInvoiceDetailDto;
                    detail.invoice_id = dto.id;
                    if (detail.id) {
                        await queryRunner.manager.createQueryBuilder()
                            .update(InvoiceDetail)
                            .set(omit(detail, ['id', 'invoice_id', 'isEdit']))
                            .where('id = :id', { id: detail.id })
                            .execute();
                        ids.push(detail.id);
                    } else {
                        const insertResult = await queryRunner.manager.createQueryBuilder()
                            .insert()
                            .into(InvoiceDetail)
                            .values(omit(detail, ['isEdit']))
                            .execute();
                        ids.push(insertResult.raw.insertId);
                    }

                }
            }
            await queryRunner.manager.createQueryBuilder().delete().from(InvoiceDetail)
                .where('invoice_id = :invoice_id', { invoice_id: dto.id })
                .andWhere('id not in (:...ids)', { ids: ids })
                .execute();

            await queryRunner.commitTransaction();
            const invoice = await this.datasource
                .createQueryBuilder<Invoice>(Invoice, 'invoice')
                .leftJoinAndSelect('invoice.details', 'invoice_detail')
                .where('invoice.id = :id', { id: dto.id })
                .getOne();

            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['保存成功'],
                obj: invoice,
            }
        } catch (err) {
            queryRunner.rollbackTransaction();
            throw err;
        } finally {
            queryRunner.release();
        }
    }
    async deleteInvoice(dto: DeleteDto): Promise<ResponseJson> {
        return await this.crud.delete<Invoice>(Invoice, dto);
    }
    async queryInvoice(
        query: SqlQueryDto,
    ): Promise<ResponseJson<VInvoice[], number>> {
        query.left = [
            {
                property: 'v_invoice.details',
                alias: 'invoice_detail',
            },
        ];
        return await this.crud.query<VInvoice>(VInvoice, query);
    }
    /**
     * 有人民币的从c_rmb_rate取，两种外币的从 c_exchange_rate取
     * @param sourceCurrency 
     * @param targetCurrency 
     * @param queryRunner 
     */
    async getRate(sourceCurrency: string, targetCurrency: string, queryRunner: QueryRunner): Promise<number | undefined> {
        let r_rate: number;
        if (sourceCurrency === 'RMB' || targetCurrency === 'RMB') {
            let foreignCurrency = sourceCurrency === 'RMB' ? targetCurrency : sourceCurrency;
            const r = await queryRunner.manager.createQueryBuilder<Currency>(Currency, 'c_rmb_rate')
                .where('currency_cod = :currency_cod', { currency_cod: foreignCurrency })
                .getOne();
            if (r) {
                if (sourceCurrency === foreignCurrency) { // 外币转人民币
                    r_rate = parseFloat(r.rate.toFixed(4));
                } else {
                    r_rate = parseFloat((1 / r.rate).toFixed(4));
                }
            }
        } else {
            let r1 = await queryRunner.manager.createQueryBuilder<ExchangeRate>(ExchangeRate, 'c_exchange_rate')
                .where('from_currency = :from_currency', { from_currency: sourceCurrency })
                .andWhere('to_currency = :to_currency', { to_currency: targetCurrency })
                .getOne();
            if (r1) {
                r_rate = parseFloat(r1.rate.toFixed(4));
            } else {
                r1 = await queryRunner.manager.createQueryBuilder<ExchangeRate>(ExchangeRate, 'c_exchange_rate')
                    .where('from_currency = :from_currency', { from_currency: targetCurrency })
                    .andWhere('to_currency = :to_currency', { to_currency: sourceCurrency })
                    .getOne();
                if (r1) {
                    r_rate = parseFloat((1 / r1.rate).toFixed(4));
                }
            }
        }
        return r_rate;
    }
    /**
     * 核销  发票--pay
     * @param auditobj
     * @returns
     */
    async auditInvoicePay(
        auditobj: AuditInvoicePayDto,
    ): Promise<ResponseJson<any, null>> {
        if (auditobj.force_audit === 'Y' && isEmpty(auditobj.audit_remark)) {
            throw new Error('强制核销必须输入备注');
        }
        const auditNo = uuidv4();
        const sysDate = new Date();

        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const invoices = await queryRunner.manager
                .createQueryBuilder<VInvoice>(VInvoice, 'v_invoice')
                .where('id in (:...ids)', { ids: auditobj.invoiceIds })
                .getMany();

            const pays = await queryRunner.manager
                .createQueryBuilder<VPay>(VPay, 'v_pay')
                .where('id in (:...ids)', { ids: auditobj.payids })
                .getMany();
            // 第一次循环处理相同货币
            for (
                let invoiceIndex = invoices.length - 1; invoiceIndex >= 0; invoiceIndex--) {
                const invoice = invoices[invoiceIndex];

                if (pays.length === 0) {
                    break;
                }
                if (invoice.pay_audit_finish_id === 'Y') {
                    throw new Error(
                        `发票号${invoice.invoice_no}已核销完毕,不能重复核销`,
                    );
                }
                if (invoice.unaudit_pay_amount <= 0) {
                    throw new Error(
                        `发票号${invoice.invoice_no}存在错误的未核销金额`,
                    );
                }
                // 循环pays
                for (
                    let payIndex = pays.length - 1; payIndex >= 0; payIndex--) {
                    const pay = pays[payIndex];
                    if (pay.currency_cod !== invoice.currency_cod) {
                        // throw new Error('存在非人民币费用');
                        continue;
                    }
                    /* const rate = invoice.currency_cod === pay.currency_cod ? 1 : this.getRate(invoice.currency_cod, pay.currency_cod, queryRunner);
                    if (isNil(rate)) {
                        throw new Error(`无${invoice.currency_cod}到${pay.currency_cod}的汇率`);
                    } */

                    if (pay.unaudit_invoice_amount <= 0) {
                        throw new Error('选择的费用条目存在错误的未核销发票金额');
                    }

                    const auditAmount = min([
                        pay.unaudit_invoice_amount,
                        invoice.unaudit_pay_amount,
                    ]);

                    const audit = {} as unknown as InvoiceAuditPay;
                    audit.pay_id = pay.id;
                    audit.invoice_id = invoice.id;
                    audit.invoice_amount = auditAmount;
                    audit.pay_amount = auditAmount;
                    audit.audit_no = auditNo;
                    audit.audit_tim = sysDate;
                    audit.force_flag = BooleanType.N;
                    await this.createInvoicePayAudit(audit, queryRunner);
                    pay.unaudit_invoice_amount =
                        pay.unaudit_invoice_amount - auditAmount;
                    invoice.unaudit_pay_amount =
                        invoice.unaudit_pay_amount - auditAmount;
                    if (pay.unaudit_invoice_amount <= 0) {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(Pay)
                            .set({ invoice_audit_id: 'Y', invoice_audit_finish_id: 'Y' })
                            .where('id = :id', { id: pay.id })
                            .execute();
                        pays.splice(payIndex, 1);
                    } else {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(Pay)
                            .set({ invoice_audit_id: 'Y' })
                            .where('id = :id', { id: pay.id })
                            .execute();
                    }
                    if (invoice.unaudit_pay_amount <= 0) {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(Invoice)
                            .set({ pay_audit_id: 'Y', pay_audit_finish_id: 'y' })
                            .where('id = :id', { id: invoice.id })
                            .execute();
                        invoices.splice(payIndex, 1);
                        break;
                    } else {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(Invoice)
                            .set({ pay_audit_id: 'Y' })
                            .where('id = :id', { id: invoice.id })
                            .execute();
                    }
                }
            }
            // 第二次循环处理不同货币
            for (
                let invoiceIndex = invoices.length - 1; invoiceIndex >= 0; invoiceIndex--) {
                const invoice = invoices[invoiceIndex];

                if (pays.length === 0) {
                    break;
                }
                if (invoice.pay_audit_finish_id === 'Y') {
                    throw new Error(
                        `发票号${invoice.invoice_no}已核销完毕,不能重复核销`,
                    );
                }
                if (invoice.unaudit_pay_amount <= 0) {
                    throw new Error(
                        `发票号${invoice.invoice_no}存在错误的未核销金额`,
                    );
                }
                // 循环pays
                for (
                    let payIndex = pays.length - 1; payIndex >= 0; payIndex--) {
                    const pay = pays[payIndex];
                    if (pay.currency_cod === invoice.currency_cod) {
                        continue;
                    }
                    const rate = invoice.currency_cod === pay.currency_cod ? 1 : (await this.getRate(invoice.currency_cod, pay.currency_cod, queryRunner));
                    if (isNil(rate)) {
                        throw new Error(`无${invoice.currency_cod}到${pay.currency_cod}的汇率`);
                    }

                    if (pay.unaudit_invoice_amount <= 0) {
                        throw new Error('选择的费用条目存在错误的未核销发票金额');
                    }

                    const auditAmount = min([
                        pay.unaudit_invoice_amount,
                        invoice.unaudit_pay_amount * rate,
                    ]);

                    const audit = {} as unknown as InvoiceAuditPay;
                    audit.pay_id = pay.id;
                    audit.invoice_id = invoice.id;
                    audit.invoice_amount = round(
                        auditAmount / rate,
                        2,
                    );
                    audit.pay_amount = auditAmount;
                    audit.audit_no = auditNo;
                    audit.audit_tim = sysDate;
                    audit.force_flag = BooleanType.N;
                    await this.createInvoicePayAudit(audit, queryRunner);
                    pay.unaudit_invoice_amount =
                        pay.unaudit_invoice_amount - audit.pay_amount;
                    invoice.unaudit_pay_amount =
                        invoice.unaudit_pay_amount - audit.invoice_amount;
                    if (pay.unaudit_invoice_amount <= 0) {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(Pay)
                            .set({ invoice_audit_id: 'Y', invoice_audit_finish_id: 'Y' })
                            .where('id = :id', { id: pay.id })
                            .execute();
                        pays.splice(payIndex, 1);
                    } else {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(Pay)
                            .set({ invoice_audit_id: 'Y' })
                            .where('id = :id', { id: pay.id })
                            .execute();
                    }
                    if (invoice.unaudit_pay_amount <= 0) {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(Invoice)
                            .set({ pay_audit_id: 'Y', pay_audit_finish_id: 'Y' })
                            .where('id = :id', { id: invoice.id })
                            .execute();
                        invoices.splice(payIndex, 1);
                        break;
                    } else {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(Invoice)
                            .set({ pay_audit_id: 'Y' })
                            .where('id = :id', { id: invoice.id })
                            .execute();
                    }
                }
            }
            // 第三次循环处理强制核销
            if (auditobj.force_audit === 'Y') {
                for (
                    let invoiceIndex = 0;
                    invoiceIndex < invoices.length;
                    invoiceIndex++
                ) {
                    const invoice = invoices[invoiceIndex];
                    const audit = {} as unknown as InvoiceAuditPay;
                    // audit.pay_id = pay.id;
                    audit.invoice_id = invoice.id;
                    // audit.pay_amount = pay.unaudit_amount;
                    audit.invoice_amount = invoice.unaudit_pay_amount;
                    // audit.feedet_amount = auditAmount;
                    // audit.currency_rate = 1;
                    audit.audit_no = auditNo;
                    audit.audit_tim = sysDate;
                    audit.force_flag = BooleanType.Y;
                    audit.remark = auditobj.audit_remark;
                    await this.createInvoicePayAudit(audit, queryRunner);
                    await queryRunner.manager
                        .createQueryBuilder()
                        .update(Invoice)
                        .set({ pay_audit_id: 'Y', pay_audit_finish_id: 'Y' })
                        .where('id = :id', { id: invoice.id })
                        .execute();
                }
                for (let payIndex = 0; payIndex < pays.length; payIndex++) {
                    const pay = pays[payIndex];

                    const audit = {} as unknown as InvoiceAuditPay;
                    audit.pay_id = pay.id;
                    audit.pay_amount = pay.unaudit_invoice_amount;
                    audit.audit_no = auditNo;
                    audit.audit_tim = sysDate;
                    audit.force_flag = BooleanType.Y;
                    audit.remark = auditobj.audit_remark;
                    await this.createInvoicePayAudit(audit, queryRunner);
                    await queryRunner.manager
                        .createQueryBuilder()
                        .update(Pay)
                        .set({ invoice_audit_id: 'Y', invoice_audit_finish_id: 'Y' })
                        .where('id = :id', { id: pay.id })
                        .execute();
                }
            }
            await queryRunner.commitTransaction();
            const auditPayResult = await this.datasource
                .createQueryBuilder<InvoiceAuditPay>(
                    InvoiceAuditPay,
                    'invoice_audit_pay',
                )
                .select(['SUM(invoice_audit_pay.pay_amount) as sum'])
                .where('audit_no = :audit_no', { audit_no: auditNo })
                .andWhere('pay_id is not null')
                .getRawOne();
            // console.log(auditPayResult);

            const auditInvoiceResult = await this.datasource
                .createQueryBuilder<InvoiceAuditPay>(
                    InvoiceAuditPay,
                    'invoice_audit_pay',
                )
                .select(['SUM(invoice_audit_pay.invoice_amount) as sum'])
                .where('audit_no = :audit_no', { audit_no: auditNo })
                .andWhere('invoice_id is not null')
                .getRawOne();

            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['核销成功'],
                obj: {
                    pay: auditPayResult,
                    invoice: auditInvoiceResult,
                },
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async queryVAuditInvoicePayGroup(
        query: SqlQueryDto,
    ): Promise<ResponseJson<VAuditInvoicePayGroup[], number>> {
        return await this.crud.query<VAuditInvoicePayGroup>(
            VAuditInvoicePayGroup,
            query,
        );
    }
    async queryVAuditInvoicePayDetail(
        query: SqlQueryDto,
    ): Promise<ResponseJson<VAuditInvoicePayDetail[], number>> {
        return await this.crud.query<VAuditInvoicePayDetail>(
            VAuditInvoicePayDetail,
            query,
        );
    }
    async cancelAuditInvoicePay(
        dto: DeleteStrPrimaryDto,
    ): Promise<ResponseJson<null, null>> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const pays = await queryRunner.manager
                .createQueryBuilder<InvoiceAuditPay>(
                    InvoiceAuditPay,
                    'invoice_audit_pay',
                )
                .select('DISTINCT invoice_audit_pay.pay_id')
                .where('audit_no in (:...audit_no)', { audit_no: dto.ids })
                .getRawMany();
            const invoices = await queryRunner.manager
                .createQueryBuilder<InvoiceAuditPay>(
                    InvoiceAuditPay,
                    'invoice_audit_pay',
                )
                .select('DISTINCT invoice_audit_pay.invoice_id')
                .where('audit_no in (:...audit_no)', { audit_no: dto.ids })
                .getRawMany();
            // console.log(pays);
            const payids = [];
            const invoiceids = [];
            for (let payIndex = 0; payIndex < pays.length; payIndex++) {
                if (pays[payIndex]['pay_id']) {
                    payids.push(pays[payIndex]['pay_id']);
                }
            }
            for (
                let invoiceIndex = 0;
                invoiceIndex < invoices.length;
                invoiceIndex++
            ) {
                if (invoices[invoiceIndex]['invoice_id']) {
                    invoiceids.push(invoices[invoiceIndex]['invoice_id']);
                }
            }

            await queryRunner.manager
                .createQueryBuilder()
                .delete()
                .from(InvoiceAuditPay)
                .where('audit_no in (:...audit_no)', { audit_no: dto.ids })
                .execute();

            await queryRunner.manager
                .createQueryBuilder()
                .update(Pay)
                .set({ invoice_audit_id: 'N' })
                .where('id in (:...ids)', { ids: payids })
                .execute();
            await queryRunner.manager
                .createQueryBuilder()
                .update(Invoice)
                .set({ pay_audit_id: 'N' })
                .where('id in (:...ids)', { ids: invoiceids })
                .execute();

            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['撤销成功'],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    /**
     * 核销  发票--feedet
     * @param auditobj
     * @returns
     */
    async auditInvoiceFeedet(
        auditobj: AuditInvoiceFeedetDto,
    ): Promise<ResponseJson<any, null>> {
        if (auditobj.force_audit === 'Y' && isEmpty(auditobj.audit_remark)) {
            throw new Error('强制核销必须输入备注');
        }
        const auditNo = uuidv4();
        const sysDate = new Date();

        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const invoices = await queryRunner.manager
                .createQueryBuilder<VInvoice>(VInvoice, 'v_invoice')
                .where('id in (:...ids)', { ids: auditobj.invoiceIds })
                .getMany();

            const feedets = await queryRunner.manager
                .createQueryBuilder<VFeedetAuditInvoice>(
                    VFeedetAuditInvoice,
                    'v_feedet_audit_invoice',
                )
                .where('id in (:...ids)', { ids: auditobj.feedetids })
                .getMany();
            // 第一次循环处理相同货币
            for (
                let invoiceIndex = invoices.length - 1;
                invoiceIndex >= 0;
                invoiceIndex--
            ) {
                const invoice = invoices[invoiceIndex];

                if (feedets.length === 0) {
                    break;
                }
                if (invoice.feedet_audit_finish_id === 'Y') {
                    throw new Error(
                        `发票号${invoice.invoice_no}已核销完毕,不能重复核销`,
                    );
                }
                if (invoice.unaudit_feedet_amount <= 0) {
                    throw new Error(
                        `发票号${invoice.invoice_no}存在错误未核销金额`,
                    );
                }
                // 循环feedets
                for (
                    let feedetIndex = feedets.length - 1;
                    feedetIndex >= 0;
                    feedetIndex--
                ) {
                    const feedet = feedets[feedetIndex];
                    if (feedet.currency_cod !== invoice.currency_cod) {
                        // throw new Error('存在非人民币费用');
                        continue;
                    }
                    if (feedet.unaudit_invoice_amount <= 0) {
                        throw new Error('存在错误的未核销发票金额');
                    }
                    const auditAmount = min([
                        feedet.unaudit_invoice_amount,
                        invoice.unaudit_feedet_amount,
                    ]);

                    const audit = {} as unknown as InvoiceAuditFeedet;
                    audit.feedet_id = feedet.id;
                    audit.invoice_id = invoice.id;
                    audit.invoice_amount = auditAmount;
                    audit.feedet_amount = auditAmount;
                    audit.audit_no = auditNo;
                    audit.audit_tim = sysDate;
                    audit.force_flag = BooleanType.N;
                    await this.createInvoiceFeedetAudit(audit, queryRunner);
                    feedet.unaudit_invoice_amount =
                        feedet.unaudit_invoice_amount - auditAmount;
                    invoice.unaudit_feedet_amount =
                        invoice.unaudit_feedet_amount - auditAmount;
                    if (feedet.unaudit_invoice_amount <= 0) {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(FeeDet)
                            .set({ invoice_audit_id: 'Y', invoice_audit_finish_id: 'Y' })
                            .where('id = :id', { id: feedet.id })
                            .execute();
                        feedets.splice(feedetIndex, 1);
                    } else {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(FeeDet)
                            .set({ invoice_audit_id: 'Y' })
                            .where('id = :id', { id: feedet.id })
                            .execute();
                    }
                    if (invoice.unaudit_feedet_amount <= 0) {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(Invoice)
                            .set({ feedet_audit_id: 'Y', feedet_audit_finish_id: 'Y' })
                            .where('id = :id', { id: invoice.id })
                            .execute();
                        invoices.splice(invoiceIndex, 1);
                        break;
                    } else {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(Invoice)
                            .set({ feedet_audit_id: 'Y' })
                            .where('id = :id', { id: invoice.id })
                            .execute();
                    }
                }
            }
            // 第二次循环处理不同货币
            for (
                let invoiceIndex = invoices.length - 1;
                invoiceIndex >= 0;
                invoiceIndex--
            ) {
                const invoice = invoices[invoiceIndex];

                if (feedets.length === 0) {
                    break;
                }
                if (invoice.feedet_audit_finish_id === 'Y') {
                    throw new Error(
                        `发票号${invoice.invoice_no}已核销完毕,不能重复核销`,
                    );
                }
                if (invoice.unaudit_feedet_amount <= 0) {
                    throw new Error(
                        `发票号${invoice.invoice_no}存在错误未核销金额`,
                    );
                }
                // 循环feedets
                for (
                    let feedetIndex = feedets.length - 1;
                    feedetIndex >= 0;
                    feedetIndex--
                ) {
                    const feedet = feedets[feedetIndex];
                    if (feedet.currency_cod === invoice.currency_cod) {
                        continue;
                    }
                    const rate = invoice.currency_cod === feedet.currency_cod ? 1 : (await this.getRate(invoice.currency_cod, feedet.currency_cod, queryRunner));
                    if (isNil(rate)) {
                        throw new Error(`无${invoice.currency_cod}到${feedet.currency_cod}的汇率`);
                    }
                    if (feedet.unaudit_invoice_amount <= 0) {
                        throw new Error('存在错误的未核销发票金额');
                    }
                    const auditAmount = min([
                        feedet.unaudit_invoice_amount,
                        invoice.unaudit_feedet_amount * rate,
                    ]);

                    const audit = {} as unknown as InvoiceAuditFeedet;
                    audit.feedet_id = feedet.id;
                    audit.invoice_id = invoice.id;
                    audit.invoice_amount = round(
                        auditAmount / rate,
                        2,
                    );
                    audit.feedet_amount = auditAmount;
                    audit.audit_no = auditNo;
                    audit.audit_tim = sysDate;
                    audit.force_flag = BooleanType.N;
                    await this.createInvoiceFeedetAudit(audit, queryRunner);
                    feedet.unaudit_invoice_amount =
                        feedet.unaudit_invoice_amount - audit.feedet_amount;
                    invoice.unaudit_feedet_amount =
                        invoice.unaudit_feedet_amount - audit.invoice_amount;
                    if (feedet.unaudit_invoice_amount <= 0) {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(FeeDet)
                            .set({ invoice_audit_id: 'Y', invoice_audit_finish_id: 'Y' })
                            .where('id = :id', { id: feedet.id })
                            .execute();
                        feedets.splice(feedetIndex, 1);
                    } else {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(FeeDet)
                            .set({ invoice_audit_id: 'Y' })
                            .where('id = :id', { id: feedet.id })
                            .execute();
                    }
                    if (invoice.unaudit_feedet_amount <= 0) {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(Invoice)
                            .set({ feedet_audit_id: 'Y', feedet_audit_finish_id: 'Y' })
                            .where('id = :id', { id: invoice.id })
                            .execute();
                        invoices.splice(invoiceIndex, 1);
                        break;
                    } else {
                        await queryRunner.manager
                            .createQueryBuilder()
                            .update(Invoice)
                            .set({ feedet_audit_id: 'Y' })
                            .where('id = :id', { id: invoice.id })
                            .execute();
                    }
                }
            }
            // 第三次循环处理强制核销
            if (auditobj.force_audit === 'Y') {
                for (
                    let invoiceIndex = 0;
                    invoiceIndex < invoices.length;
                    invoiceIndex++
                ) {
                    const invoice = invoices[invoiceIndex];
                    const audit = {} as unknown as InvoiceAuditFeedet;
                    // audit.pay_id = pay.id;
                    audit.invoice_id = invoice.id;
                    // audit.pay_amount = pay.unaudit_amount;
                    audit.invoice_amount = invoice.unaudit_feedet_amount;
                    // audit.feedet_amount = auditAmount;
                    // audit.currency_rate = 1;
                    audit.audit_no = auditNo;
                    audit.audit_tim = sysDate;
                    audit.force_flag = BooleanType.Y;
                    audit.remark = auditobj.audit_remark;
                    await this.createInvoiceFeedetAudit(audit, queryRunner);
                    await queryRunner.manager
                        .createQueryBuilder()
                        .update(Invoice)
                        .set({ feedet_audit_id: 'Y', feedet_audit_finish_id: 'Y' })
                        .where('id = :id', { id: invoice.id })
                        .execute();
                }
                for (
                    let feedetIndex = 0;
                    feedetIndex < feedets.length;
                    feedetIndex++
                ) {
                    const feedet = feedets[feedetIndex];

                    const audit = {} as unknown as InvoiceAuditFeedet;
                    audit.feedet_id = feedet.id;
                    audit.feedet_amount = feedet.unaudit_invoice_amount;
                    audit.audit_no = auditNo;
                    audit.audit_tim = sysDate;
                    audit.force_flag = BooleanType.Y;
                    audit.remark = auditobj.audit_remark;
                    await this.createInvoiceFeedetAudit(audit, queryRunner);
                    await queryRunner.manager
                        .createQueryBuilder()
                        .update(FeeDet)
                        .set({ invoice_audit_id: 'Y', feedet_audit_finish_id: 'Y' })
                        .where('id = :id', { id: feedet.id })
                        .execute();
                }
            }
            await queryRunner.commitTransaction();
            const auditPayResult = await this.datasource
                .createQueryBuilder<InvoiceAuditFeedet>(
                    InvoiceAuditFeedet,
                    'invoice_audit_feedet',
                )
                .select(['SUM(invoice_audit_feedet.feedet_amount) as sum'])
                .where('audit_no = :audit_no', { audit_no: auditNo })
                .andWhere('feedet_id is not null')
                .getRawOne();
            // console.log(auditPayResult);

            const auditInvoiceResult = await this.datasource
                .createQueryBuilder<InvoiceAuditFeedet>(
                    InvoiceAuditFeedet,
                    'invoice_audit_feedet',
                )
                .select(['SUM(invoice_audit_feedet.invoice_amount) as sum'])
                .where('audit_no = :audit_no', { audit_no: auditNo })
                .andWhere('invoice_id is not null')
                .getRawOne();

            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['核销成功'],
                obj: {
                    feedet: auditPayResult,
                    invoice: auditInvoiceResult,
                },
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async queryVAuditInvoiceFeedetGroup(
        query: SqlQueryDto,
    ): Promise<ResponseJson<VAuditInvoiceFeedetGroup[], number>> {
        return await this.crud.query<VAuditInvoiceFeedetGroup>(
            VAuditInvoiceFeedetGroup,
            query,
        );
    }
    async queryVAuditInvoiceFeedetDetail(
        query: SqlQueryDto,
    ): Promise<ResponseJson<VAuditInvoiceFeedetDetail[], number>> {
        return await this.crud.query<VAuditInvoiceFeedetDetail>(
            VAuditInvoiceFeedetDetail,
            query,
        );
    }
    async cancelAuditInvoiceFeedet(
        dto: DeleteStrPrimaryDto,
    ): Promise<ResponseJson<null, null>> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const feedets = await queryRunner.manager
                .createQueryBuilder<InvoiceAuditFeedet>(
                    InvoiceAuditFeedet,
                    'invoice_audit_feedet',
                )
                .select('DISTINCT invoice_audit_feedet.feedet_id')
                .where('audit_no in (:...audit_no)', { audit_no: dto.ids })
                .getRawMany();
            const invoices = await queryRunner.manager
                .createQueryBuilder<InvoiceAuditFeedet>(
                    InvoiceAuditFeedet,
                    'invoice_audit_feedet',
                )
                .select('DISTINCT invoice_audit_feedet.invoice_id')
                .where('audit_no in (:...audit_no)', { audit_no: dto.ids })
                .getRawMany();
            // console.log(pays);
            const feedetids = [];
            const invoiceids = [];
            for (
                let feedetIndex = 0;
                feedetIndex < feedets.length;
                feedetIndex++
            ) {
                if (feedets[feedetIndex]['feedet_id']) {
                    feedetids.push(feedets[feedetIndex]['feedet_id']);
                }
            }
            for (
                let invoiceIndex = 0;
                invoiceIndex < invoices.length;
                invoiceIndex++
            ) {
                if (invoices[invoiceIndex]['invoice_id']) {
                    invoiceids.push(invoices[invoiceIndex]['invoice_id']);
                }
            }

            await queryRunner.manager
                .createQueryBuilder()
                .delete()
                .from(InvoiceAuditFeedet)
                .where('audit_no in (:...audit_no)', { audit_no: dto.ids })
                .execute();

            await queryRunner.manager
                .createQueryBuilder()
                .update(FeeDet)
                .set({ invoice_audit_id: 'N' })
                .where('id in (:...ids)', { ids: feedetids })
                .execute();
            await queryRunner.manager
                .createQueryBuilder()
                .update(Invoice)
                .set({ feedet_audit_id: 'N' })
                .where('id in (:...ids)', { ids: invoiceids })
                .execute();

            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['撤销成功'],
            };
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    /**
     * 
     * @param dto 
     * @returns 
     */
    async FeedetReport(
        dto: BusiReportDto,
    ): Promise<ResponseJson<any[], null>> {
        const ownerfeedets = await this.datasource
            .createQueryBuilder<FeeDet>(FeeDet, 'fee_det')
            .select([
                'uuid_short() as id',
                'fee_det.fee_owner as fee_owner',
                'fee_det.currency_cod as currency_cod',
                "sum(if(fee_det.fee_in_out = 'I' , fee_det.amount, 0)) as in_amount",
                "sum(if(fee_det.fee_in_out = 'O' , fee_det.amount, 0)) as out_amount",
                "sum(if(fee_det.fee_in_out = 'I' , fee_det.amount, 0)) - sum(if(fee_det.fee_in_out = 'O' , fee_det.amount, 0)) as income",
            ])
            .where('fee_det.fee_tim between :start_tim and :end_tim', {
                start_tim: dto.start_tim,
                end_tim: dto.end_tim,
            })
            .groupBy('fee_det.fee_owner')
            .addGroupBy('fee_det.currency_cod')
            .getRawMany();
        for (
            let ownerindex = 0;
            ownerindex < ownerfeedets.length;
            ownerindex++
        ) {
            const ownerfeedet = ownerfeedets[ownerindex];
            const typfeedets = await this.datasource
                .createQueryBuilder<FeeDet>(FeeDet, 'fee_det')
                .select([
                    'uuid_short() as id',
                    'fee_det.fee_owner as fee_owner',
                    'fee_det.fee_typ_cod as fee_typ_cod',
                    'fee_det.currency_cod as currency_cod',
                    "sum(if(fee_det.fee_in_out = 'I' , fee_det.amount, 0)) as in_amount",
                    "sum(if(fee_det.fee_in_out = 'O' , fee_det.amount, 0)) as out_amount",
                    "sum(if(fee_det.fee_in_out = 'I' , fee_det.amount, 0)) - sum(if(fee_det.fee_in_out = 'O' , fee_det.amount, 0)) as income",
                ])
                .where('fee_det.fee_tim between :start_tim and :end_tim', {
                    start_tim: dto.start_tim,
                    end_tim: dto.end_tim,
                })
                .andWhere('fee_det.fee_owner = :fee_owner', {
                    fee_owner: ownerfeedet.fee_owner,
                })
                .andWhere('fee_det.currency_cod = :currency_cod', {
                    currency_cod: ownerfeedet.currency_cod,
                })
                .groupBy('fee_det.fee_owner')
                .addGroupBy('fee_det.fee_typ_cod')
                .addGroupBy('fee_det.currency_cod')
                .getRawMany();
            for (let typindex = 0; typindex < typfeedets.length; typindex++) {
                const typfeedet = typfeedets[typindex];
                const codfeedets = await this.datasource
                    .createQueryBuilder<FeeDet>(FeeDet, 'fee_det')
                    .select([
                        'uuid_short() as id',
                        'fee_det.fee_owner as fee_owner',
                        'fee_det.fee_typ_cod as fee_typ_cod',
                        'fee_det.fee_cod as fee_cod',
                        'fee_det.currency_cod as currency_cod',
                        "sum(if(fee_det.fee_in_out = 'I' , fee_det.amount, 0)) as in_amount",
                        "sum(if(fee_det.fee_in_out = 'O' , fee_det.amount, 0)) as out_amount",
                        "sum(if(fee_det.fee_in_out = 'I' , fee_det.amount, 0)) - sum(if(fee_det.fee_in_out = 'O' , fee_det.amount, 0)) as income",
                    ])
                    .where('fee_det.fee_tim between :start_tim and :end_tim', {
                        start_tim: dto.start_tim,
                        end_tim: dto.end_tim,
                    })
                    .andWhere('fee_det.fee_owner = :fee_owner', {
                        fee_owner: typfeedet.fee_owner,
                    })
                    .andWhere('fee_det.fee_typ_cod = :fee_typ_cod', {
                        fee_typ_cod: typfeedet.fee_typ_cod,
                    })
                    .andWhere('fee_det.currency_cod = :currency_cod', {
                        currency_cod: typfeedet.currency_cod,
                    })
                    .groupBy('fee_det.fee_owner')
                    .addGroupBy('fee_det.fee_typ_cod')
                    .addGroupBy('fee_det.fee_cod')
                    .addGroupBy('fee_det.currency_cod')
                    .getRawMany();
                typfeedet['children'] = codfeedets;
            }
            ownerfeedet['children'] = typfeedets;
        }

        // console.log(ownerfeedets);
        return {
            code: ResponseCode.SuccessNoMsg,
            obj: ownerfeedets,
        };
    }
    /**
     * 银行存款月度汇总表
     * @param dto 
     * @returns 
     */
    async categoryReport(dto: BusinessReportDto): Promise<ResponseJson<any[], null>> {
        const month = dto.month;
        const startTim = dayjs(month).startOf('month');
        const endTim = dayjs(month).endOf('month');


        const endTimStr = endTim.format('YYYY-MM-DD HH:mm:ss');
        const r = await this.datasource.createQueryBuilder()
            .from<Category>(Category, 'c_category')
            .select(['c_category.id as category', 'c_category.currency_cod as currency_cod', `getCategorySurplus(c_category.id, '${endTimStr}') as surplus`])
            .leftJoinAndSelect((qb: SelectQueryBuilder<VCategoryDetail>) => {
                const subQuery = qb.subQuery()
                    .from<VCategoryDetail>(VCategoryDetail, 'v_category_detail')
                    .select(['v_category_detail.pay_typ as pay_typ', "sum(if(v_category_detail.fee_in_out = 'I', v_category_detail.amount, 0)) as month_add",
                        "sum(if(v_category_detail.fee_in_out = 'O', v_category_detail.amount, 0)) as month_reduce"])
                    .where('v_category_detail.fee_tim between :startTim and :endTim', { startTim: startTim.toDate(), endTim: endTim.toDate() })
                    .groupBy('v_category_detail.pay_typ')
                return subQuery;
            }, 'a', 'c_category.id = a.pay_typ')
            .getRawMany();

        return {
            code: ResponseCode.SuccessNoMsg,
            obj: r,
        };
    }
    /**
     * 银行存款月度明细表
     * @param dto 
     * @returns 
     */
    async categoryDetailReport(dto: BusinessReportDto): Promise<ResponseJson<any[], null>> {
        const month = dto.month;
        const startTim = dayjs(month).startOf('month');
        const endTim = dayjs(month).endOf('month');


        const endTimStr = endTim.format('YYYY-MM-DD HH:mm:ss');

        const r = await this.datasource.createQueryBuilder<VCategoryDetail>(VCategoryDetail, 'v_category_detail')
            .where('v_category_detail.fee_tim between :startTim and :endTim', { startTim: startTim.toDate(), endTim: endTim.toDate() })
            .andWhere('v_category_detail.pay_typ = :category_id', { category_id: dto.category_id })
            .getMany();

        return {
            code: ResponseCode.SuccessNoMsg,
            obj: r,
        };
    }
    /**
     * 资产类往来明细表、负债类往来明细表
     * @param dto 
     */
    async assetClassReport(dto: BusinessReportDto, report_type: string): Promise<ResponseJson<any[], null>> {
        const fee_owner = dto.fee_owner;
        const cur_month = dto.month;
        const feetyp = [];
        if (report_type === 'A') { // 资产类
            feetyp.push('07');
            feetyp.push('09');
            feetyp.push('08');
        } else { // 负债类
            feetyp.push('13');
            feetyp.push('19');
            feetyp.push('16');
        }

        const pre_month = dayjs(cur_month).subtract(1, 'month');
        const pre_start = dayjs(pre_month).startOf('month');
        const pre_end = dayjs(pre_month).endOf('month');
        const cur_start = dayjs(cur_month).startOf('month');
        const cur_end = dayjs(cur_month).endOf('month');
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            // 创建临时表
            await queryRunner.manager.query(`
                create temporary table if not exists assetClassReport (
                    id varchar(18) ,
                    fee_typ_cod  varchar(4) ,
                    fee_unit_cod int unsigned ,
                    currency_cod varchar(3) ,
                    pre_surplus decimal(14, 2)  ,
                    cur_amount decimal(14, 2)  ,
                    cur_surplus decimal(14, 2) ,
                    PRIMARY KEY (fee_typ_cod, fee_unit_cod))`);
            await queryRunner.manager.query(`
                delete from assetClassReport
                    `);
            for (let index = 0; index < feetyp.length; index++) {
                const feetypcod = feetyp[index];
                // 上期结余数
                const feedet07prequery = await queryRunner.manager.createQueryBuilder<VAuditFeedet>(VAuditFeedet, 'v_audit_feedet')
                    .select(['uuid_short() as id', `'${feetypcod}' as fee_typ_cod`, 'v_audit_feedet.fee_unit_cod as fee_unit_cod',
                        'v_audit_feedet.currency_cod as currency_cod', "sum(v_audit_feedet.unaudit_amount) as pre_surplus", 'null as cur_amount',
                        'null as cur_surplus'])
                    .where("v_audit_feedet.fee_typ_cod = :fee_typ_cod", { fee_typ_cod: feetypcod })
                    .andWhere('v_audit_feedet.fee_tim between :start and :end', { start: pre_start.toDate(), end: pre_end.toDate() })
                    .groupBy('v_audit_feedet.fee_unit_cod')
                    .addGroupBy('v_audit_feedet.currency_cod');
                if (fee_owner) {
                    feedet07prequery.andWhere('fee_owner = :fee_owner', { fee_owner });
                }
                const feedet07_presurplus = await feedet07prequery.getRawMany();
                await queryRunner.manager.createQueryBuilder()
                    .insert()
                    .into('assetClassReport')
                    .values(feedet07_presurplus)
                    .execute();
                //  本月发生数
                const feedet07curquery = await queryRunner.manager.createQueryBuilder<FeeDet>(FeeDet, 'fee_det')
                    .select(['uuid_short() as id', `'${feetypcod}' as fee_typ_cod`, 'fee_det.fee_unit_cod as fee_unit_cod',
                        'fee_det.currency_cod as currency_cod', 'null as pre_surplus', "sum(fee_det.amount) as cur_amount",
                        'null as cur_surplus'])
                    .where("fee_det.fee_typ_cod = :fee_typ_cod", { fee_typ_cod: feetypcod })
                    .andWhere('fee_det.fee_tim between :start and :end', { start: cur_start.toDate(), end: cur_end.toDate() })
                    .groupBy('fee_det.fee_unit_cod')
                    .addGroupBy('fee_det.currency_cod');
                if (fee_owner) {
                    feedet07curquery.andWhere('fee_owner = :fee_owner', { fee_owner });
                }
                const feedet07_curamount = await feedet07curquery.getRawMany();
                await queryRunner.manager.createQueryBuilder()
                    .insert()
                    .into('assetClassReport')
                    .values(feedet07_curamount)
                    .orUpdate(['cur_amount'])
                    .execute();
                //  本月结余数
                const feedet07cursurplusquery = await queryRunner.manager.createQueryBuilder<VAuditFeedet>(VAuditFeedet, 'v_audit_feedet')
                    .select(['uuid_short() as id', `'${feetypcod}' as fee_typ_cod`, 'v_audit_feedet.fee_unit_cod as fee_unit_cod',
                        'v_audit_feedet.currency_cod as currency_cod', 'null as pre_surplus', 'null as cur_amount',
                        "sum(v_audit_feedet.unaudit_amount) as cur_surplus"])
                    .where("v_audit_feedet.fee_typ_cod = :fee_typ_cod", { fee_typ_cod: feetypcod })
                    .andWhere('v_audit_feedet.fee_tim <= :end', { end: cur_end.toDate() })
                    .groupBy('v_audit_feedet.fee_unit_cod')
                    .addGroupBy('v_audit_feedet.currency_cod');
                if (fee_owner) {
                    feedet07cursurplusquery.andWhere('fee_owner = :fee_owner', { fee_owner });
                }
                const feedet07_cursurplus = await feedet07cursurplusquery.getRawMany();
                await queryRunner.manager.createQueryBuilder()
                    .insert()
                    .into('assetClassReport')
                    .values(feedet07_cursurplus)
                    .orUpdate(['cur_surplus'])
                    .execute();

            }


            // 


            const r = await queryRunner.manager.createQueryBuilder()
                .from('assetClassReport', 'assetClassReport')
                .getRawMany();
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessNoMsg,
                obj: r,
            }
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    /**
     * 成本费用明细表
     * @param dto 
     * @returns 
     */
    async feedetoutReport(dto: BusinessReportDto): Promise<ResponseJson<any[], null>> {
        const month = dto.month;
        const startTim = dayjs(month).startOf('month');
        const endTim = dayjs(month).endOf('month');
        const fee_owner = dto.fee_owner;

        const startTimStr = dayjs(month).startOf('year').format('YYYY-MM-DD HH:mm:ss');
        const endTimStr = dayjs(month).endOf('year').format('YYYY-MM-DD HH:mm:ss');

        const r = await this.datasource.createQueryBuilder()
            .select(['all_year.fee_typ_cod as fee_typ_cod', 'all_year.fee_cod as fee_cod', 'all_year.currency_cod as currency_cod',
                'all_year.all_amount as all_amount'])
            .from((subQuery1) => {
                const subAllQuery =
                    subQuery1.select(['fee_det.fee_typ_cod as fee_typ_cod', 'fee_det.fee_cod as fee_cod', 'fee_det.currency_cod as currency_cod',
                        `getFeedetOut(fee_det.fee_typ_cod, fee_det.fee_cod, fee_det.currency_cod, '${startTimStr}',  '${endTimStr}', ${fee_owner ?? null}) as all_amount`])
                        .from(FeeDet, 'fee_det')
                        .groupBy('fee_det.fee_typ_cod')
                        .addGroupBy('fee_det.fee_cod')
                        .addGroupBy('fee_det.currency_cod');
                if (fee_owner) {
                    subAllQuery.where('fee_det.fee_owner = :fee_owner', { fee_owner: fee_owner });
                }
                return subAllQuery;
            }, 'all_year')

            .leftJoinAndSelect((qb: SelectQueryBuilder<FeeDet>) => {
                const subQuery = qb.subQuery()
                    .from<FeeDet>(FeeDet, 'fee_det1')
                    .select(['fee_det1.fee_typ_cod as fee_typ_cod', 'fee_det1.fee_cod as fee_cod', 'fee_det1.currency_cod as currency_cod',
                        'sum(fee_det1.amount) as cur_amount'])
                    .where('fee_det1.fee_tim between :startTim and :endTim', { startTim: startTim.toDate(), endTim: endTim.toDate() })
                    .andWhere("fee_det1.fee_in_out = 'O' ")
                    .groupBy('fee_det1.fee_typ_cod')
                    .addGroupBy('fee_det1.fee_cod')
                    .addGroupBy('fee_det1.currency_cod');
                if (fee_owner) {
                    subQuery.andWhere('fee_det1.fee_owner = :fee_owner', { fee_owner: fee_owner });
                }
                return subQuery;
            }, 'a', 'all_year.fee_typ_cod = a.fee_typ_cod and all_year.fee_cod = a.fee_cod and all_year.currency_cod = a.currency_cod')
            .orderBy('all_year.fee_typ_cod')
            .addOrderBy('all_year.fee_cod')
            .addOrderBy('all_year.currency_cod')
            .getRawMany();


        return {
            code: ResponseCode.SuccessNoMsg,
            obj: r,
        };
    }
    async importFeedet(file: any, oper_nam: string): Promise<ResponseJson<FeeDet[], null>> {
        const r = [];
        const workbook = new Excel.Workbook();
        await workbook.xlsx.readFile(file.path);
        if (workbook.worksheets.length <= 0) {
            throw new Error('文件格式错误');
        }
        const firstWorksheet = workbook.worksheets[0];
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const filerows = firstWorksheet.rowCount;
            for (let index = 2; index <= filerows; index++) {
                const row = firstWorksheet.getRow(index);
                const cntr = row.getCell(1).value.toString().trim();
                if (isNil(cntr) || cntr.length !== 11) {
                    throw new Error(`第${index}行，箱号错误`);
                }
                const in_truck_sn = parseInt(row.getCell(2).value.toString().trim());
                if (!Number.isFinite(in_truck_sn)) {
                    throw new Error(`第${index}行，箱进场计划号错误`);
                }
                const find_cntr = await queryRunner.manager.createQueryBuilder<Cntr>(Cntr, 'cntr_file')
                    .select(['id'])
                    .where('cntr = :cntr', { cntr: cntr })
                    .andWhere(" in_truck_sn = :in_truck_sn", { in_truck_sn: in_truck_sn })
                    .getRawOne();
                // console.log('cntr_no', cntr_no);

                if (isNil(find_cntr)) {
                    throw new Error(`箱号${cntr}，箱号未定位在进场计划号下`);
                }
                const cntr_no = find_cntr.id
                const currency_cod = row.getCell(3).value.toString().trim();
                const currency_check = (await queryRunner.manager.createQueryBuilder<Currency>(Currency, 'c_rmb_rate')
                    .select(['count(1) as currency_count'])
                    .where('currency_cod = :currency_cod', { currency_cod: currency_cod })
                    .getRawOne())['currency_count'];


                if (currency_check != 1) {
                    throw new Error(`货币${currency_cod}系统不存在，请维护基础数据`);
                }
                const amount = parseFloat(row.getCell(4).value.toString().trim());

                const fee_unit_cod = (await queryRunner.manager.createQueryBuilder<Client>(Client, 'c_client_cod')
                    .select(['c_client_cod.id as id']).where('client_nam_c = :client_nam', { client_nam: row.getCell(5).value.toString().trim() })
                    .getRawOne())['id'];

                const fee_owner = (await queryRunner.manager.createQueryBuilder<Client>(Client, 'c_client_cod')
                    .select(['c_client_cod.id as id']).where('client_nam_c = :client_nam', { client_nam: row.getCell(6).value.toString().trim() })
                    .getRawOne())['id'];

                if (row.getCell(7).value.toString().trim() !== '收款' && row.getCell(7).value.toString().trim() !== '付款') {
                    throw new Error(`箱号${cntr}收付款类型错误`);
                }
                const fee_in_out = row.getCell(7).value.toString().trim() === '收款' ? 'I' : 'O';

                const fee_typ_cod = (await queryRunner.manager.createQueryBuilder<FeeTyp>(FeeTyp, 'c_fee_typ')
                    .select(['c_fee_typ.fee_typ_cod as fee_typ_cod']).where('fee_typ_nam = :fee_typ_nam', { fee_typ_nam: row.getCell(8).value.toString().trim() })
                    .getRawOne())['fee_typ_cod'];
                const fee_cod = (await queryRunner.manager.createQueryBuilder<FeeCod>(FeeCod, 'c_fee_cod')
                    .select(['c_fee_cod.fee_cod as fee_cod']).where('fee_nam = :fee_nam', { fee_nam: row.getCell(9).value.toString().trim() })
                    .getRawOne())['fee_cod'];

                const fee_tim = new Date(row.getCell(10).value as string);
                if (fee_tim.getTime() > new Date().getTime()) {
                    throw new Error(`箱号${cntr}${row.getCell(9).value.toString().trim()}费用时间不能早于当前时间`);
                }
                const checkFee = await queryRunner.manager.createQueryBuilder<FeeDet>(FeeDet, 'fee_det')
                    .select(['count(1) as checknum'])
                    .where('cntr_no = :cntr_no', { cntr_no: cntr_no })
                    .andWhere('fee_in_out = :fee_in_out', { fee_in_out: fee_in_out })
                    .andWhere('fee_typ_cod = :fee_typ_cod', { fee_typ_cod: fee_typ_cod })
                    .andWhere('fee_cod = :fee_cod', { fee_cod })
                    .getRawOne();
                if (checkFee['checknum'] > 0) {
                    throw new Error(`箱号${cntr}${row.getCell(9).value.toString().trim()}已存在`);
                }
                const feedetObj = {} as unknown as FeedetDto;
                feedetObj.cntr_no = cntr_no;
                feedetObj.cntr = cntr;
                feedetObj.fee_unit_cod = fee_unit_cod;
                feedetObj.fee_owner = fee_owner;
                feedetObj.fee_typ_cod = fee_typ_cod;
                feedetObj.fee_cod = fee_cod;
                feedetObj.amount = amount;
                feedetObj.currency_cod = currency_cod;
                feedetObj.fee_nam = oper_nam;
                feedetObj.fee_tim = fee_tim;
                feedetObj.fee_in_out = fee_in_out;
                // console.log(feedetObj);
                const feedetresult = await this.createFeedet(feedetObj, queryRunner);
                r.push(feedetresult.obj);
            }
            await queryRunner.commitTransaction();
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }


        return {
            code: ResponseCode.SuccessNoMsg,
            msg: ['导入成功'],
            obj: r,
        }
    }
    /**
     * 经营报表
     * @param dto 
     */
    async feedetEcharts(dto: BusiReportDto): Promise<ResponseJson<any, string[]>> {
        const startTim = dayjs(dto.start_tim).startOf('month');
        const endTim = dayjs(dto.end_tim).endOf('month');
        const months = getMonthsBetween(startTim.toDate(), endTim.toDate());

        const infee: {
            month: string,
            value: string
        }[] = [];
        const outfee: {
            month: string,
            value: string
        }[] = [];
        // 应收付
        const inoutFeedetSql = await this.datasource.createQueryBuilder<FeeDet>(FeeDet, 'fee_det')
            .leftJoin('fee_det.currency', 'c_rmb_rate')
            .select(["date_format(fee_det.fee_tim, '%Y%m') as month", 'fee_det.fee_in_out as fee_in_out', "ROUND(sum(fee_det.amount * ifnull(c_rmb_rate.rate, 1)),2) as value"])
            .where('fee_det.fee_tim between :start_tim and :end_tim', {
                start_tim: startTim.toDate(), end_tim: endTim.toDate()
            })
            .orderBy('month')
            .addOrderBy('fee_det.fee_in_out')
            .groupBy('month')
            .addGroupBy('fee_det.fee_in_out');

        if (!isNil(dto.fee_owner)) {
            inoutFeedetSql.andWhere('fee_owner = :fee_owner', { fee_owner: dto.fee_owner });
        }
        const inoutFeedets = await inoutFeedetSql.getRawMany();
        for (let index = 0; index < inoutFeedets.length; index++) {
            const element = inoutFeedets[index];
            if (element.fee_in_out === 'I') {
                infee.push({
                    month: element.month,
                    value: element.value
                });
            } else {
                outfee.push({
                    month: element.month,
                    value: element.value
                });
            }
        }
        // 应收 fee_typ
        const inTypeSql = await this.datasource.createQueryBuilder<FeeDet>(FeeDet, 'fee_det')
            .leftJoin('fee_det.currency', 'c_rmb_rate')
            .select(["fee_det.fee_typ_cod as fee_typ_cod", "ROUND(sum(fee_det.amount * ifnull(c_rmb_rate.rate, 1)),2) as value"])
            .where('fee_det.fee_tim between :start_tim and :end_tim', {
                start_tim: startTim.toDate(), end_tim: endTim.toDate()
            })
            .andWhere("fee_det.fee_in_out = 'I' ")
            .groupBy('fee_typ_cod');

        if (!isNil(dto.fee_owner)) {
            inTypeSql.andWhere('fee_owner = :fee_owner', { fee_owner: dto.fee_owner });
        }
        const inType = await inTypeSql.getRawMany();
        // 应收 fee_typ
        const outTypeSql = await this.datasource.createQueryBuilder<FeeDet>(FeeDet, 'fee_det')
            .leftJoin('fee_det.currency', 'c_rmb_rate')
            .select(["fee_det.fee_typ_cod as fee_typ_cod", "ROUND(sum(fee_det.amount * ifnull(c_rmb_rate.rate, 1)),2) as value"])
            .where('fee_det.fee_tim between :start_tim and :end_tim', {
                start_tim: startTim.toDate(), end_tim: endTim.toDate()
            })
            .andWhere("fee_det.fee_in_out = 'O' ")
            .groupBy('fee_typ_cod');

        if (!isNil(dto.fee_owner)) {
            outTypeSql.andWhere('fee_owner = :fee_owner', { fee_owner: dto.fee_owner });
        }
        const outType = await outTypeSql.getRawMany();
        return {
            code: ResponseCode.SuccessNoMsg,
            obj: {
                infee, outfee, inType, outType
            },
            ext: months
        };

    }
    async feeReportDefineQuery(): Promise<ResponseJson<FeeReportManager[], number>> {
        const query: any = {};
        query.left = [
            {
                property: 'c_fee_report.cfgs',
                alias: 'c_fee_report_cfg',
            },
        ];
        return await this.crud.query<FeeReportManager>(FeeReportManager, query);
    }
    async feeReportDefineCreate(dto: FeeReportDefineDto): Promise<ResponseJson<FeeReportManager, null>> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const insertPlanResult = await queryRunner.manager
                .createQueryBuilder()
                .insert()
                .into(FeeReportManager)
                .values(omit(dto, ['fee_cods']))
                .execute();
            const report_id = insertPlanResult.identifiers[0]['id'];
            for (let index = 0; index < dto.fee_cods.length; index++) {
                const fee_cod = dto.fee_cods[index];
                await queryRunner.manager.createQueryBuilder()
                    .insert()
                    .into(FeeReportCfg)
                    .values({
                        report_id: report_id,
                        fee_cod: fee_cod
                    })
                    .execute();
            }
            await queryRunner.commitTransaction();
            const newReport = await this.datasource.createQueryBuilder<FeeReportManager>(FeeReportManager, 'c_fee_report')
                .leftJoinAndSelect(
                    'c_fee_report.cfgs',
                    'c_fee_report_cfg',
                )
                .where('c_fee_report.id = :id', { id: report_id })
                .getOne();

            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['创建成功'],
                obj: newReport
            }
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
        // return await this.crud.create<FeeMod, FeeModDto>(FeeMod, dto);
    }

    async feeReportDefineUpdate(dto: UpdateFeeReportDefineDto): Promise<ResponseJson<FeeReportManager, null>> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const r = await queryRunner.manager.createQueryBuilder()
                .update(FeeReportManager)
                .set(omit(dto, ['id', 'fee_cods']))
                .where('id = :id', { id: dto.id })
                .execute();
            await queryRunner.manager.createQueryBuilder()
                .delete()
                .from(FeeReportCfg)
                .where('report_id = :report_id', { report_id: dto.id })
                .execute();
            if (dto.fee_cods) {
                for (let index = 0; index < dto.fee_cods.length; index++) {
                    const element = dto.fee_cods[index];
                    await queryRunner.manager.createQueryBuilder()
                        .insert()
                        .into(FeeReportCfg)
                        .values({
                            report_id: dto.id,
                            fee_cod: element
                        })
                        .execute();
                }
            }
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['修改成功']
            }
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }

    async feeReportDefineDelete(dto: DeleteDto): Promise<ResponseJson> {

        return await this.crud.delete<FeeReportManager>(FeeReportManager, dto);
    }

    async getFeeReportFeeCods(reportName: string, q?: QueryRunner): Promise<string[]> {
        const queryRunner = q ?? this.datasource.createQueryRunner();
        const feeCods = await queryRunner.manager
            .createQueryBuilder<FeeReportCfg>(FeeReportCfg, 'c_fee_report_cfg')
            .innerJoin('c_fee_report_cfg.report', 'report')
            .select(['c_fee_report_cfg.fee_cod as fee_cod'])
            .where('report.r_name = :r_name', { r_name: reportName })
            .execute();
        const feecods = [];
        for (let index = 0; index < feeCods.length; index++) {
            const element = feeCods[index];
            feecods.push(element.fee_cod);
        }

        // console.log(feecods);
        return feecods;

    }
    async getFeeCodsFromFeeReport(reportName: string): Promise<ResponseJson<string[], null>> {
        const feecods = await this.getFeeReportFeeCods(reportName);
        return {
            code: ResponseCode.SuccessNoMsg,
            msg: [],
            obj: feecods
        }
    }
    async leaseFeeInvoice(dto: LeaseFeeUsdInvoiceDto): Promise<ResponseJson<string, null>> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const startTim = formatDateTime(dto.start_tim);
            const endTim = formatDateTime(dto.end_tim);
            const clientCod = dto.fee_owner;
            const report = await queryRunner.manager.createQueryBuilder<FeeReportManager>(FeeReportManager, 'c_fee_report')
                .select()
                .where('r_name = :reportName', {
                    reportName: '租箱费用报表'
                })
                .getOne();
            const feecods = await this.getFeeReportFeeCods('租箱费用报表', queryRunner);
            const cntrNum = await queryRunner.query
                (
                    `select count(1) as count from (select distinct lease_cntr_id from v_lease_fee_report ` +
                    ` where fee_unit_cod = '${clientCod}'` +
                    ` and fee_in_out = '${report.fee_in_out}'` +
                    ` and (fee_tim between '${startTim}' and '${endTim}')` +
                    ` and fee_cod in (?)` +
                    ` and currency_cod = 'USD'` +
                    ` group by lease_cntr_id) a`, feecods
                );


            const totalAmount = await queryRunner.manager.createQueryBuilder<LeaseFeeReport>(LeaseFeeReport, 'v_lease_fee_report')
                .select('sum(amount)', 'amount')
                .where('fee_unit_cod = :feeUnitCod', { feeUnitCod: clientCod })
                .andWhere('fee_in_out = :feeInOut', { feeInOut: report.fee_in_out })
                .andWhere('fee_tim between :start and :end', { start: startTim, end: endTim })
                .andWhere('fee_cod in (:...feeCods)', { feeCods: feecods })
                .andWhere('currency_cod = :currency_cod', { currency_cod: 'USD' })
                .getRawOne();

            const invoiceDate = dayjs().format('YYYY-MM-DD');
            const client = await queryRunner.manager.createQueryBuilder<Client>(Client, 'c_client_cod')
                .select('client_nam_e')
                .where('id = :id', { id: dto.fee_owner })
                .getRawOne();
            const feeDateRange = dayjs(dto.start_tim).format('MMMM YYYY') + '-' + dayjs(dto.end_tim).format('MMMM YYYY');
            await queryRunner.commitTransaction();
            const wb = new Excel.Workbook();
            await wb.xlsx.readFile(
                join(this.downloadpath, 'leaseusdinvoice.xlsx')
            );
            const ws = wb.getWorksheet(1);
            ws.getCell('E7').value = invoiceDate;
            ws.getCell('A9').value = client['client_nam_e'];
            ws.getCell('C9').value = dto.invoiceNo;
            ws.getCell('A15').value = feeDateRange;
            ws.getCell('C14').value = cntrNum[0]['count'];
            ws.getCell('E14').value = '$' + totalAmount['amount'];
            ws.getCell('E18').value = '$' + totalAmount['amount'];
            ws.getCell('E28').value = '$' + totalAmount['amount'];
            const newfilename = `${uuidv4()}.xlsx`;
            const inputPath = join(this.downloadpath, newfilename);
            await wb.xlsx.writeFile(inputPath);
            const filepath = join(
                this.downloadpath,
                'lease_invoice_pdf',
                dayjs(new Date()).format('YYYYMM'),
            );
            if (!existsSync(filepath)) {
                mkdirSync(filepath, { recursive: true });
            }
            const filename = `${uuidv4()}.pdf`;

            const outputPath = join(filepath, filename);
            await this.OfficeToPdf(inputPath, outputPath);
            const downloadpath = `/download/lease_invoice_pdf/${dayjs(new Date()).format(
                'YYYYMM',
            )}/${filename}`;

            unlinkSync(join(this.downloadpath, newfilename)); // 删除生成的临时excel文件
            return {
                code: ResponseCode.SuccessNoMsg,
                msg: [''],
                obj: downloadpath
            }
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async OfficeToPdf(inputPath: string, outputPath: string) {
        const ext = '.pdf';
        // const inputPath = path.join(__dirname, 'logo.xlsx');
        // const outputPath = path.join(__dirname, `logo${ext}`);

        // Read file
        const docxBuf = readFileSync(inputPath);

        // Convert it to pdf format with undefined filter (see Libreoffice docs about filter)
        let pdfBuf = await (libre as any)['convertAsync'](
            docxBuf,
            ext,
            undefined,
        );

        // Here in done you have pdf file which you can save or transfer in another stream
        await writeFileSync(outputPath, pdfBuf);
    }
    async leaseFeeReport(dto: LeaseFeeReportDto): Promise<ResponseJson<any, any>> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const r: ResponseJson<any, any> = {
                code: ResponseCode.SuccessNoMsg,
                msg: [''],
                ext: {
                    total: 0,
                    columnOpt: []
                },
            };
            const startTim = formatDateTime(dto.start_tim);
            const endTim = formatDateTime(dto.end_tim);
            const clientCod = dto.fee_owner;

            const report = await queryRunner.manager.createQueryBuilder<FeeReportManager>(FeeReportManager, 'c_fee_report')
                .select()
                .where('r_name = :reportName', {
                    reportName: '租箱费用报表'
                })
                .getOne();
            const feecods = await this.getFeeReportFeeCods('租箱费用报表', queryRunner);
            if (feecods.length === 0) {
                throw new Error('报表未定义费目');
            }
            const columnOpt = await queryRunner.manager.createQueryBuilder<LeaseFeeReport>(LeaseFeeReport, 'v_lease_fee_report')
                .select('distinct fee_cod, currency_cod')
                .where('fee_unit_cod = :feeUnitCod', { feeUnitCod: clientCod })
                .andWhere('fee_in_out = :feeInOut', { feeInOut: report.fee_in_out })
                .andWhere('fee_tim between :start and :end', { start: startTim, end: endTim })
                .andWhere('fee_cod in (:...feeCods)', { feeCods: feecods })
                .orderBy('fee_cod, currency_cod')
                .getRawMany();
            // console.log(columnOpt);

            if (columnOpt) {
                r.ext.columnOpt = columnOpt;
            }

            const feeBUilder = queryRunner.manager.createQueryBuilder<LeaseFeeReport>(LeaseFeeReport, 'v_lease_fee_report')
                .select(['lease_cntr_id as id', 'cntr', 'cntr_siz_cod', 'cntr_typ_cod', 'out_cfs', 'bill_no as lease_no', 'out_date as fee_start_date',
                    `ifnull(back_date, '${endTim}') as fee_end_date`, 'back_date', 'back_cfs', 'day_rate as rate', 'lease_currency_cod'
                ])
            for (let index = 0; index < columnOpt.length; index++) {
                const element = columnOpt[index];
                if (element.fee_cod === '0105') { // 租箱费
                    feeBUilder.addSelect(`sum(if(currency_cod = '${element.currency_cod}' and fee_cod = '${element.fee_cod}', fee_ton, 0)) `, `days`);
                }
                feeBUilder.addSelect(`sum(if(currency_cod = '${element.currency_cod}' and fee_cod = '${element.fee_cod}', amount, 0)) `, `${element.currency_cod}${element.fee_cod}`);
            }

            const fees = await feeBUilder.take(dto.take).skip(dto.skip)
                .where('fee_unit_cod = :feeUnitCod', { feeUnitCod: clientCod })
                .andWhere('fee_in_out = :feeInOut', { feeInOut: report.fee_in_out })
                .andWhere('fee_tim between :start and :end', { start: startTim, end: endTim })
                .andWhere('fee_cod in (:...feeCods)', { feeCods: feecods })
                .groupBy('lease_cntr_id, cntr, cntr_siz_cod, cntr_typ_cod, out_cfs, bill_no, out_date, back_date, back_cfs, day_rate, lease_currency_cod')
                .getRawMany();
            if (dto.skip === 0) {
                // 计算行数
                const total_num = await queryRunner.manager.createQueryBuilder()
                    .select('count(1)', 'count')
                    .from(subQuery => {
                        return subQuery.select('distinct lease_cntr_id')
                            .from(LeaseFeeReport, 'v_lease_fee_report')
                            .where('fee_unit_cod = :feeUnitCod', { feeUnitCod: clientCod })
                            .andWhere('fee_in_out = :feeInOut', { feeInOut: report.fee_in_out })
                            .andWhere('fee_tim between :start and :end', { start: startTim, end: endTim })
                            .andWhere('fee_cod in (:...feeCods)', { feeCods: feecods })
                            .groupBy('lease_cntr_id')
                        // .getQuery()
                    }, 'v_lease_fee_report')
                    .getRawOne();
                r.ext.total = total_num['count'];
                // 计算汇总
                const feeTotalBUilder = queryRunner.manager.createQueryBuilder<LeaseFeeReport>(LeaseFeeReport, 'v_lease_fee_report');
                for (let index = 0; index < columnOpt.length; index++) {
                    const element = columnOpt[index];
                    let colnam = '';
                    if (element.fee_cod === '0105') { // 租箱费
                        // feeBUilder.addSelect(`sum(if(currency_cod = '${element.currency_cod}' and fee_cod = '${element.fee_cod}', fee_ton, 0)) `, `days`);
                        colnam = `sum(if(currency_cod = '${element.currency_cod}' and fee_cod = '${element.fee_cod}', fee_ton, 0)) as days`;
                    }
                    // feeBUilder.addSelect(`sum(if(currency_cod = '${element.currency_cod}' and fee_cod = '${element.fee_cod}', amount, 0)) `, `${element.currency_cod}${element.fee_cod}`);
                    colnam = `sum(if(currency_cod = '${element.currency_cod}' and fee_cod = '${element.fee_cod}', amount, 0)) as ${element.currency_cod}${element.fee_cod}`;
                    if (index === 0) {
                        feeTotalBUilder.select(colnam);
                    } else {
                        feeTotalBUilder.addSelect(colnam);
                    }
                }
                const totalfees = await feeTotalBUilder
                    .where('fee_unit_cod = :feeUnitCod', { feeUnitCod: clientCod })
                    .andWhere('fee_in_out = :feeInOut', { feeInOut: report.fee_in_out })
                    .andWhere('fee_tim between :start and :end', { start: startTim, end: endTim })
                    .andWhere('fee_cod in (:...feeCods)', { feeCods: feecods })
                    .getRawMany();
                r.ext.sum = totalfees[0];
            }
            r.obj = fees;

            await queryRunner.commitTransaction();
            return r;
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
    async cfsFeeReport(dto: LeaseFeeReportDto): Promise<ResponseJson<any, any>> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const r: ResponseJson<any, any> = {
                code: ResponseCode.SuccessNoMsg,
                msg: [''],
                ext: {
                    total: 0,
                    columnOpt: []
                },
            };
            const startTim = formatDateTime(dto.start_tim);
            const endTim = formatDateTime(dto.end_tim);
            const clientCod = dto.fee_owner;

            const report = await queryRunner.manager.createQueryBuilder<FeeReportManager>(FeeReportManager, 'c_fee_report')
                .select()
                .where('r_name = :reportName', {
                    reportName: '场站费用报表'
                })
                .getOne();
            const feecods = await this.getFeeReportFeeCods('场站费用报表', queryRunner);
            if (feecods.length === 0) {
                throw new Error('报表未定义费目');
            }
            const columnOpt = await queryRunner.manager.createQueryBuilder<CfsFeeReport>(CfsFeeReport, 'v_cntr_fee_report')
                .select('distinct fee_cod, currency_cod')
                .where('fee_unit_cod = :feeUnitCod', { feeUnitCod: clientCod })
                .andWhere('fee_in_out = :feeInOut', { feeInOut: report.fee_in_out })
                .andWhere('fee_tim between :start and :end', { start: startTim, end: endTim })
                .andWhere('fee_cod in (:...feeCods)', { feeCods: feecods })
                .orderBy('fee_cod, currency_cod')
                .getRawMany();
            // console.log(columnOpt);

            if (columnOpt) {
                r.ext.columnOpt = columnOpt;
            }

            const feeBUilder = queryRunner.manager.createQueryBuilder<CfsFeeReport>(CfsFeeReport, 'v_cntr_fee_report')
                .select(['id', 'cntr', 'cntr_siz_cod', 'cntr_typ_cod',]);
            for (let index = 0; index < columnOpt.length; index++) {
                const element = columnOpt[index];
                if (element.fee_cod === '0108') { // 堆存费
                    feeBUilder.addSelect(`sum(if(currency_cod = '${element.currency_cod}' and fee_cod = '${element.fee_cod}', fee_ton, 0)) `, `cal_days`);
                }
                feeBUilder.addSelect(`sum(if(currency_cod = '${element.currency_cod}' and fee_cod = '${element.fee_cod}', amount, 0)) `, `${element.currency_cod}${element.fee_cod}`);
            }

            const fees = await feeBUilder.take(dto.take).skip(dto.skip)
                .where('fee_unit_cod = :feeUnitCod', { feeUnitCod: clientCod })
                .andWhere('fee_in_out = :feeInOut', { feeInOut: report.fee_in_out })
                .andWhere('fee_tim between :start and :end', { start: startTim, end: endTim })
                .andWhere('fee_cod in (:...feeCods)', { feeCods: feecods })
                .groupBy('id, cntr, cntr_siz_cod, cntr_typ_cod ')
                .getRawMany();
            for (let index = 0; index < fees.length; index++) {
                const cntrObj = fees[index];
                const cntrStats = await queryRunner.manager.createQueryBuilder<PortCntrStat>(PortCntrStat, 'port_cntr_stat')
                    // .select(['cntr_stat_cod', 'stat_tim', 'current_stat', 'cfs_cod', 'cal_stack'])
                    .select()
                    .where('cntr_no = :cntr_no', { cntr_no: cntrObj.id })
                    .orderBy('stat_tim')
                    .getMany();
                for (let statIndex = 0; statIndex < cntrStats.length; statIndex++) {
                    const cntrStat = cntrStats[statIndex];
                    if (cntrStat.cfs_cod === clientCod && cntrStat.current_stat === '2' && !cntrObj['in_cfs_tim']) { // 进场
                        cntrObj['in_cfs_tim'] = cntrStat.stat_tim;
                    }
                    if (cntrStat.cfs_cod === clientCod && cntrStat.current_stat !== '2') { // 进场
                        cntrObj['out_cfs_tim'] = cntrStat.stat_tim;
                        cntrObj['stack_days'] = (cntrObj['stack_days'] ?? 0) + dayjs(cntrObj['out_cfs_tim']).diff(dayjs(cntrObj['in_cfs_tim']), 'day');
                    }
                    if (!cntrObj['stack_days']) {
                        cntrObj['stack_days'] = dayjs(dto.end_tim).diff(dayjs(cntrObj['in_cfs_tim']), 'day');
                    }
                }
            }
            if (dto.skip === 0) {
                // 计算总行数
                const total_num = await queryRunner.manager.createQueryBuilder()
                    .select('count(1)', 'count')
                    .from(subQuery => {
                        return subQuery.select('distinct id')
                            .from(CfsFeeReport, 'v_cntr_fee_report')
                            .where('fee_unit_cod = :feeUnitCod', { feeUnitCod: clientCod })
                            .andWhere('fee_in_out = :feeInOut', { feeInOut: report.fee_in_out })
                            .andWhere('fee_tim between :start and :end', { start: startTim, end: endTim })
                            .andWhere('fee_cod in (:...feeCods)', { feeCods: feecods })
                            .groupBy('id')
                        // .getQuery()
                    }, 'v_cntr_fee_report')
                    .getRawOne();
                r.ext.total = total_num['count'];
                // 计算汇总
                const feeTotalBUilder = queryRunner.manager.createQueryBuilder<CfsFeeReport>(CfsFeeReport, 'v_cntr_fee_report');
                for (let index = 0; index < columnOpt.length; index++) {
                    const element = columnOpt[index];
                    let colnam = '';
                    if (element.fee_cod === '0108') { // 堆存费
                        // feeTotalBUilder.addSelect(`sum(if(currency_cod = '${element.currency_cod}' and fee_cod = '${element.fee_cod}', fee_ton, 0)) `, `cal_days`);
                        colnam = `sum(if(currency_cod = '${element.currency_cod}' and fee_cod = '${element.fee_cod}', fee_ton, 0)) as cal_days`;
                    }

                    //feeTotalBUilder.addSelect(`sum(if(currency_cod = '${element.currency_cod}' and fee_cod = '${element.fee_cod}', amount, 0)) `, `${element.currency_cod}${element.fee_cod}`);
                    colnam = `sum(if(currency_cod = '${element.currency_cod}' and fee_cod = '${element.fee_cod}', amount, 0)) as ${element.currency_cod}${element.fee_cod}`
                    if (index === 0) {
                        feeTotalBUilder.select(colnam);
                    } else {
                        feeTotalBUilder.addSelect(colnam);
                    }
                }

                const totalfees = await feeTotalBUilder
                    .where('fee_unit_cod = :feeUnitCod', { feeUnitCod: clientCod })
                    .andWhere('fee_in_out = :feeInOut', { feeInOut: report.fee_in_out })
                    .andWhere('fee_tim between :start and :end', { start: startTim, end: endTim })
                    .andWhere('fee_cod in (:...feeCods)', { feeCods: feecods })
                    .getRawMany();
                r.ext.sum = totalfees[0];
            }

            r.obj = fees;


            // columnOpt [ { fee_cod: '0105', currency_cod: 'USD' } ]
            // console.log('columnOpt', columnOpt);

            await queryRunner.commitTransaction();
            return r;
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
    }
}
